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

11.03.2010
| 23831 views |
  • 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
select 
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
set DATEFIRST 1
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))