Did you ever faced a situation when you need quickly calculate some boundaries of the Date value like First Day of the month, Last Day of the month, etc… in T-SQL?
Colegue of mine asked me this question… WOW! What do you know…
It is actually very easy if we think about it for a second…
So lets fun begin…
--First Day of the Current Month: select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) -- Last Day of the Current Month: select DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) -- Last Day of Prior Month: select DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) -- First Day of the Current Year: select DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) -- Last Day of the Current Year: select DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) -- Last Day of Prior Year: select DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
And some more…
-- First Day of the Quarter: select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0) -- Monday of the Current Week: select DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) -- Midnight for the Current Day: select DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) -- First Monday of the Month: select DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, 6 - DATEPART(day, GETDATE()), GETDATE())), 0) -- Last 2 full months select DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0))
4 Comments
Karen · Apr 5, 2007 at 12:06
I need to add 18 months to the below; for example, 3/1/2006 returns 9/1/2007; 5/1/2007 returns 11/1/2008.
dateadd(ms, -3, DATEADD(mm, DATEDIFF(m, 0, terminationdate) + 1, 0))
Sir. Hacksalot · Sep 11, 2012 at 16:29
Can someone please help with setting a date for (Last day of prior month, in prior year)
example: if today is 09/11/2012 the results would be 08/31/2011.
Roy · Oct 16, 2012 at 08:53
I need a querry that can return an end date from start date and number of months
VIP · Nov 26, 2012 at 12:04
select distinct
dateadd(mm,DATEDIFF(mm, 0, GETDATE()),0)
, dateadd(ss,-1,dateadd(mm,DATEDIFF(mm, 0, GETDATE())-12,0) ),
datediff(mm,dateadd(mm,DATEDIFF(mm, 0, GETDATE()),0), dateadd(ss,-1,dateadd(mm,DATEDIFF(mm, 0, GETDATE())-12,0) ))