Last Date Of Month script

  • The featured script to retrieve the last day of the month could be made simpler. The last day of a month, is the day prior to the first day of the next month; so no need to make loops. Simple add one month and step back one day:

    declare @m int

    declare @y int

    set @m = 9

    set @y = 2004

    -- the last day of a month, is the day prior to the first day of the next month

    select day(dateadd(day, -1, dateadd(month, 1, CONVERT(char(2), @m) + '/' +'01/' +  CONVERT(char(4), @Y))))

     

     

  • Last day of current month

    select day(dateadd(day, -1, dateadd(month, 1, getdate())))

  • Newbie,

    For '2004-01-30', your SQL returns 28.

    This is, obviously, not the last day of the month in question, January.

    Try this:

    Determine the first day of the specified month;

    Add 1 month;

    Subtract 1 day.

     

    DECLARE @Date datetime

    SET @Date = '2004-01-30'

    SELECT DATEADD(d, -1, DATEADD(m, 1, DATEADD(d, -1 * (DAY(@Date) - 1), @Date)))

  • This works,

    add 1 month, sub days of date

    select convert( datetime,right('00'+convert(varchar(2), month(@date)),2) +

     '/' + right('00'+convert(varchar(2), DAY(DATEADD(d, -DAY(DATEADD(m,1,@date)),DATEADD(m,1,@date)))),2) + '/' +

     convert(varchar(4),year(@date)))

     

  • Why make it harder than it needs to be?

    -- 2003-03-11 / Kenneth Wilhelmsson

    -- This is a sample matrix showing how to find out dates of month boundries

    -- from any given point in time.

    set nocount on

    declare @date datetime

    set     @date = '2004-01-30' --getdate() -- the point in time from which to measure

    print '''Today''s date'' is: ' + convert(char(10), @date, 121)

    print ' '

    -- date of the 1st of the current month

    select convert(char(6), @date, 112) + '01' as '1st this month'

    -- date of the last day of current month

    select dateadd(day, -1, dateadd(month, 1, convert(char(6), @date, 112) + '01')) as 'last this month'

    -- date of the 1st of the previous month

    select dateadd(month, -1, convert(char(6), @date, 112) + '01') as '1st of last month'

    -- date of the last day of the previous month

    select dateadd(day, -1, convert(char(6), @date, 112) + '01') as 'last of last month'

    -- date of the 1st of the next month

    select dateadd(month, 1, convert(char(6), @date, 112) + '01') as '1st of next month'

    -- date of the last day of the next month

    select dateadd(day, -1, dateadd(month, 2, convert(char(6), @date, 112) + '01')) as 'last of next month'

    set nocount off

    'Today's date' is: 2004-01-30

     

    1st this month

    --------------

    20040101

    last this month                                       

    ------------------------------------------------------

    2004-01-31 00:00:00.000

    1st of last month                                     

    ------------------------------------------------------

    2003-12-01 00:00:00.000

    last of last month                                    

    ------------------------------------------------------

    2003-12-31 00:00:00.000

    1st of next month                                     

    ------------------------------------------------------

    2004-02-01 00:00:00.000

    last of next month                                    

    ------------------------------------------------------

    2004-02-29 00:00:00.000

     

    /Kenneth

     

  • Great date samples guys! I love the archives!

    In using Kens example above, wouldn't this be better for LastDayLastMonth?

    Dateadd(Day,-Datepart(day,@TruncDate)+1,@TruncDate) -.0001 as LastDayLastMonth

    This is simply FirstDayThisMonth - .0001 thus giving me (for a getdate of 2005-01-04 09:37:50.770) a date of 2004-12-31 23:59:51.360

    I figure if someone used LastDayLastMonth in the previous post such as:

    SELECT *

    WHERE somedate IS BETWEEN FirstDayPrevMonth AND LastDayLastMonth

    Wouldn't any record timestamped after midnight on the last day of the previous month get filtered out?

    Thanks,

    David McAfee

  • SELECT *

    WHERE somedate IS BETWEEN FirstDayPrevMonth AND LastDayLastMonth

    Wouldn't any record timestamped after midnight on the last day of the previous month get filtered out?

    Yes, they will get filterered out. That's a pitfall when querying dates that contain time fractions. To do this properly you need to use something like

    SELECT *

    WHERE somedate > = FirstDayPrevMonth AND < FirstDayCurrentMonth

    In case you're not doing this within a view, but rather a stored procedure, you can use this pretty cool script by SQL Server MVP Steve Kass to determine the last day of a month:

    declare @31st datetime

    set @31st = '19341031' -- any 31st

    declare @now datetime

    set @now = getdate()

    select dateadd(month,datediff(month,@31st,@now),@31st)

                                                          

    ------------------------------------------------------

    2005-01-31 00:00:00.000

    (1 row(s) affected)

    The coolest thing here is that you can virtually use any allowed 31st (note: no other day!) as a starting date. No matter if past, present, or future.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • My personal favorite is (for all you Brits out there) –

    Select convert(varchar(10),dateadd(month,1+datediff(month,0,getdate()),0)-1,103) AS [EOM]

  • wow, I tried it for next month (February) and it returns the 28th, pretty cool.

    declare @31st datetime

    set @31st = '19341031' -- any 31st

    declare @now datetime

    set @now = dateadd(month,1,getdate())

    select dateadd(month,datediff(month,@31st,@now),@31st)

    ----------------------------------------

    2005-02-28 00:00:00.000

  • Guess I'll add my two (or is it 3?) cents...

    ------ Finds the LAST day of CURRENT month with no time element
    SELECT DATEADD(mm,DATEDIFF(mm,0,GetDate())+1,0)-1 AS LastDayCurrentMonth
    ------ Finds the LAST day of CURRENT month (Time=23:59:59.997) (resolution is 3 ms)
    SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GetDate())+1,0)) AS LastDayCurrentMonth
    ------ Finds the FIRST day of CURRENT month (Time=00:00:00.000)
    SELECT DATEADD(mm,DATEDIFF(mm,0,@Date),0) AS FirstDayCurrentMonth
    
    
     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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