RSS 2.0 Feed
RSS 2.0


Atom 1.0 Feed
Atom 1.0

  On the Subject of Dates in T-SQL 


While we're on the subject of dates in T-SQL, I never liked getting the month and year for a date and sticking an '01' in the middle (then casting it all back to a datetime) to get the first day of the month for a given date value. Then you do the same to get the end date by getting the first day of the next month and subtract a 1 from it. This way is much better. And by better I mean cooler ;-)

declare @date datetime
set @date = getdate()

--get first day of month 
select dateadd(m, datediff(m, 0, @date), 0)

--get last day of month 
select dateadd(m, datediff(m, 0, dateadd(m, 1, @date)), -1)




                   



Leave a comment below.

Comments

  1. Ravinder 3/14/2005 6:06 AM
    Gravatar

    your script failing for feb month of Leap year
    for example @date = '2/1/2004' will give you the last day as 2004-01-31 00:00:00.000 which is wrong
  2. Ryan Farley 3/14/2005 12:01 PM
    Gravatar
    Ravinder,

    Strange. It works for me. When I use that code for the date "2/1/2004" I am returned "2004-02-01 00:00:00.000" for the last day of the month. I wonder if your regional date settings are efffecting the output? Like I said, it works for me (on several production systems).

    -Ryan
  3. Branimir 3/30/2005 12:45 AM
    Gravatar
    It does'nt work for if I set '20050201' instead getdate(). Start date is 2005-02-01 and end date is 2005-01-31
  4. Patman 6/13/2005 10:50 AM
    Gravatar
    Assuming that the return values should be
    1) the first day of the given month
    2) the last day of the given month
    the script given for #2 is incorrect. It should read:
    --get last day of month
    select dateadd(m, datediff(m, 0, dateadd(m, 1, @date) <b>+</b> 1), -1)

    This will fix Ravinder's problem of it returning the last day of the previous month.

    I am confused how "When I use that code for the date "2/1/2004" I am returned "2004-02-01 00:00:00.000" for the last day of the month. " considering the first should always be the "first" (not last) day of any month ;)

    Cheers!
  5. Ryan Farley 7/18/2005 3:01 PM
    Gravatar
    Patman,

    Actually that's a typo in my post, it should read:

    select dateadd(m, datediff(m, 0, dateadd(m, 1, @date)), -1) as LastDay

    No need to add or subtract a 1 from the nested dateadd. I'll fix that now.

    -Ryan
  6. George Helyar 11/21/2005 10:46 AM
    Gravatar
    one day before 1st feb 04 is 31st jan 04 as the leapyear is at the end of the month but

    select dateadd(m, datediff(m, 0, dateadd(m, 1, '2004-02-01')), -1)

    returns 2004-02-29 00:00:00.000 for me fine on us and uk regiional settings... (1st feb 04 in international date format)
  7. Thara 5/23/2006 11:21 PM
    Gravatar
    how can we calculate the day,month and year differences using a single statement
    ie day,month and year differences between 12/10/2004 and 02/07/2005
    Thanking You
  8. Charles 8/18/2007 8:41 PM
    Gravatar
    Problem: The example only returns the start of the last day of the month. Any transactions that occurred after midnight will not be retrieved.

    For example, the code returns: "2007-08-31 00:00:00.000" but, if this is run on 8/31 at 11:59PM (23:59) the results would not display any of the current day's transactions. The last day of the month needs to return "2007-08-31 23:59:59.999"
  9. Gil 11/5/2007 11:23 AM
    Gravatar
    To solve Charles' issue you could use:

    --get last day of month
    select dateadd(m, datediff(m, 0, dateadd(m, 1, @date)), -0.00000004)

    To get the time 3ms (the smallest supported interval) before the beginning of the next month.
  10. Ben McInerney 2/6/2008 5:44 AM
    Gravatar
    There is even a simplier way, and this is to get the first day of the following month, but at 00:00:00, which will yield nothing from this first day but everything upto midnight of the last day of the chosen month.

    So: ... WHERE MSP_EpmAssignmentByDay_OlapView.TimeByDay BETWEEN dateadd(m, datediff(m, 0, @StartDate), 0) AND dateadd(m, datediff(m, 0, @StartDate), 1

    Retrieves all data for the month that @StartDate belongs to.
    NOTE: this assumes you are not concerned with times, just full days.
  11. John Nevill 2/18/2009 1:07 PM
    Gravatar
    That just rocks. Thank you
  12. Gravatar
    Soma district san francisco galleries.
  13. Soma silk massage cream. 9/5/2009 12:34 AM
    Gravatar
    Soma silk massage cream.
  14. Soma 350 mg.. 9/5/2009 8:26 PM
    Gravatar
    Soma 350 mg.. What is the usual dose of 350 mg soma. Soma 350 mg.
  15. Chico soma. 9/6/2009 6:24 AM
    Gravatar
    Chico soma.
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:              


Sponsor

Sections