Handy SQL Code Snippets

I admit that I can be a bit lazy with my SQL coding and sometimes I do borrow handy snippets to get the job done. I should probably have memorised some of these or at least turned them into functions that can be called when required but I’ve not managed that yet (on the list of things to do).

Unfortunately, one of my favourite sites for date code snippets no longer keeps the page for T-SQL (i.e. Microsoft SQL Server) code for a raft of various relative date calculations so I am going to list them here for others who might be after them (and also myself).

Ironically, if I had turned some of these into functions I wouldn’t be in this predicament 🙂

Anyway, here is what used to be @ http://shanecooper.net/cooldatefunctions.htm

First Day of Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

First Day of Last Month
select DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)

Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

Sunday of the Current 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)

First Day of the 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
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))

Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 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)