Over a million developers have joined DZone.

Calculating Various Dates Using DATEADD, DATEDIFF And GETDATE

· IoT Zone

Discover how Cisco DevNet can help you create innovative applications for the Internet of Things leveraging Cisco APIs and platforms, brought to you in partnership with Cisco DevNet.


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



DevIoT is an Internet of Things development environment enabling frictionless design and deployment of complex IoT solutions.  Curious? Learn how you can easily design and deploy IoT applications with Cisco DevIoT.

Topics:
dateadd,datediff,getdate

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

{{ parent.tldr }}

{{ parent.urlSource.name }}