On the Subject of Dates in T-SQL

Tuesday, February 15, 2005 8:07 AM Filed under:

General

SQL/Data

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)

Comments

Ravinder 3/14/2005 6:06 AM

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
Ryan Farley 3/14/2005 12:01 PM

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
Branimir 3/30/2005 12:45 AM

It does'nt work for if I set '20050201' instead getdate(). Start date is 2005-02-01 and end date is 2005-01-31
Patman 6/13/2005 10:50 AM

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!
Ryan Farley 7/18/2005 3:01 PM

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
George Helyar 11/21/2005 10:46 AM

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)
Thara 5/23/2006 11:21 PM

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
Charles 8/18/2007 8:41 PM

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"
Gil 11/5/2007 11:23 AM

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.
Ben McInerney 2/6/2008 5:44 AM

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.
John Nevill 2/18/2009 1:07 PM

That just rocks. Thank you
Soma district san francisco galleries. 9/4/2009 12:53 PM

Soma district san francisco galleries.
Soma silk massage cream. 9/5/2009 12:34 AM

Soma silk massage cream.
Soma 350 mg.. 9/5/2009 8:26 PM

Soma 350 mg.. What is the usual dose of 350 mg soma. Soma 350 mg.
Chico soma. 9/6/2009 6:24 AM

Chico soma.