Over a million developers have joined DZone.

Calculating Various Dates Using DATEADD, DATEDIFF And GETDATE

· IoT Zone

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



Topics:
dateadd ,datediff ,getdate

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}