DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Calculating Various Dates Using DATEADD, DATEDIFF And GETDATE

  • submit to reddit
        //All the SQL Server examples in this post only use the DATEADD and DATEDIFF functions to calculate our desired date. 
//Each example will do this by calculating date intervals from the current date, 
//and then adding or subtracting intervals to arrive at the desired calculated date.
//The technique shown here for calculating a date interval between the current date 
//and the year "1900-01-01," and then adding the calculated number of interval to "1900-01-01," 
//to calculate a specific date, can be used to calculate many different dates. The next four examples 
//use this same technique to generate different dates based on the current date.

--First and Last Day of Previous Month
DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0) as FirstDayPrevMo
,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)-1 as LastDayPrevMo
--First Day of Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as FirstDayCurrMo
--Monday of the Current Week with Sunday as first day of week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
--Monday of the Current Week with Monday as first day of week
select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())
--First Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))
--Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))
--First Monday of the Month
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)      
--Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))
--First Day of the Current Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--Midnight for the Current Day
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
--Last Day of Prior Month (3 Milisecond Method
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))