Calculating Various Dates Using DATEADD, DATEDIFF And GETDATE

• Tweet
• 

        //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))
 

Tags:
• SQL
• SQL Server
• t-sql