I am continuing putting some common examples of operations for Microsoft SQL Server.
In addition to some of the Date related code snippets published before, there are few more today:
-- Last 90 days: select DATEADD(day, -90, GETDATE()) -- Get Date portion of DateTime value select DATEADD(d, 0, DATEDIFF(d, 0, getdate()))
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))
MSDN News