Last day of Month

  • Hello All,

    How can I calculate last day of month?

    I have two parameter in stored procedure

    create procedure

    (@year int,

    @uptomonth)

    as

    begin

    ....................................

    end

    I want to calculate last day of the Month ....

    based on parameter passing in stored procedure.

    If I am passing

    @year =2011

    @uptoMonnth =9

    then ouput like '2011-09-30'

    another example like

    If I am passing

    @year =2011

    @uptoMonnth =10

    then ouput like '2011-10-31'

    Thanks

    Bhavesh

  • You have many to do this.

    One of them is to create a table an fill it up with month and last day.

    In the table just this two fields, MONTH and LASTDAY

    MONTH - LASTDAY

    1 31

    2 28

    3 31

    4 30

    ETC ETC

    so in the query to get the last day just look the month of the parameter and looked on this table.

  • SELECT TOP 1 dt

    FROM dbo.Calendar

    WHERE Y = @Year AND M = @Month

    --AND IsBusDay = 1

    ORDER BY dt DESC

    http://www.sqlservercentral.com/Forums/Attachment8839.aspx

  • Use the month and year to get the first day of that month. Then use DateAdd to add a month to, then DateAdd again to subtract one day, and you'll have the last day of the month.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • This gives you last day of the current month

    SELECT convert(varchar,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),101)

  • DECLARE @LASTDAY VARCHAR(10)

    declare @uptoMonth as int

    declare @year as int

    declare @lear as int

    set @leap = (select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime))) when 2 then 1 else 0 end)

    IF @uptoMonth IN (1,3,5,7,8,10,12)

    BEGIN

    SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-31'

    END

    IF @uptoMonth IN (4,6,9,11)

    BEGIN

    SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-30'

    END

    IF @uptoMonth = 2

    BEGIN

    IF @leap = 1

    BEGIN

    SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-29'

    END

    ELSE

    BEGIN

    SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-28'

    END

    END

    Just show the variable @LASTDAY and its done!

  • Thanks For replying me .

    If year =2012 then @Last day is 29 ...then

    what i need to chagne in this stored procedure.

    Thanks

  • bhaveshp.dba (12/2/2011)


    Thanks For replying me .

    If year =2012 then @Last day is 29 ...then

    what i need to chagne in this stored procedure.

    Thanks

    Works in my code.

    Can then by turned into itfv to use with cross/outer apply.

    Nothing wrong here with doing the math approach, untill you need the end of the month... on a week day or business dayor that is not a holiday or...

  • Hello Ninja's_RGR'us

    i tried your calander but it is not executed in my sql.

    Hello Sys..

    i tried your code and i want to know if @year is leap year then @Last day is 29.

    So how can i do with your stored procedure.

    Thanks

    Bhavesh

  • Select dateadd(month, datediff(month, -1, cast(@year as char(4)) + '-' + cast(@month as varchar(2)) + '-01'), -1)

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Keep in mind:

    A year will be a leap year if it is evenly divisible by 4 , but not 100. If a year is divisible by 4 and 100, it is NOT a leap year unless it is also divisible by 400

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks You so much,,

    Jeff

    It;s working fine.:-):-):-)

  • Here is what you need in your store procedure

    declare @uptoMonth as int

    declare @year as int

    declare @LASTDAY VARCHAR(10)

    declare @lear as int

    set @leap = (select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime))) when 2 then 1 else 0 end)

    IF @uptoMonth IN (1,3,5,7,8,10,12)

    BEGIN

    SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-31'

    END

    IF @uptoMonth IN (4,6,9,11)

    BEGIN

    SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-30'

    END

    IF @uptoMonth = 2

    BEGIN

    IF @leap = 1

    BEGIN

    SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-29'

    END

    ELSE

    BEGIN

    SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-28'

    END

    END

    Just show the variable @LASTDAY and its done!

  • OK, here's a one-liner for you:

    SELECT DATEPART(dd, DATEADD(dd, -1, CAST(CAST(@Month+1 AS VARCHAR(2)) + '/1/' + CAST(@Year AS VARCHAR(4)) AS DATETIME)))

    ...

  • bhaveshp.dba (12/2/2011)


    Hello Ninja's_RGR'us

    i tried your calander but it is not executed in my sql.

    Hello Sys..

    i tried your code and i want to know if @year is leap year then @Last day is 29.

    So how can i do with your stored procedure.

    Thanks

    Bhavesh

    Comment out the filegroup on the create table script. It'll work then

    I've wanted to edit that out for a while but I can't find the original post and I've linked to it 15 times so I can't delete it either!

Viewing 15 posts - 1 through 15 (of 44 total)

You must be logged in to reply to this topic. Login to reply