The Ultimate List of SQL Server DateTime Functions

A while back I happened upon this post by Pinal Dave, the self proclaimed SQL Authority. It is quite possibly the best list of SQL Server datetime functions ever created.

How much would you pay for such a list? One hundred dollars? One thousand dollars? Eight million dollars?! You probably would, but you are about to get it for free. You know, because I like you so much.

----Today
SELECT GETDATE()
----Yesterday
SELECT DATEADD(d,-1,GETDATE())
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))
----First Day Of Current Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0)
----Last Day Of Current Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0))
----First Day Of Prior Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0)
----Last Day Of Prior Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0))
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

Now I’m sure you are all asking, “This is great and everything, but how can I use it?” Well, what sort of friend would I be if I didnt break it down a little for you?

Lets say you want to create a employee sales comission report and will be using SSRS to email that at the end of every month. You cant use a static date like ‘2013-03-01’ because obviously that wouldnt work come next month.

To solve that issue, your select statement for your dataset might go something like this:

SELECT employeeID, SUM(ordertotal)
FROM salestable
WHERE orderstatus = 'closed' AND orderdate >= (DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
GROUP BY employeeID

See that? The Benster has you covered. For all you Top Guns out there, try using this Advanced Maneuver:

DECLARE @FirstofMonth AS smalldatetime;
SET @FirstofMonth = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

SELECT employeeID, SUM(ordertotal)
FROM salestable
WHERE orderstatus = 'closed' AND orderdate >= @FirstofMonth
GROUP BY employeeID

Good work, Iceman! We still on for vollyball later?

TOP GUN

Anywho, I hope this list and my mediocre examples find their way to others who might not have found it otherwise. Peace!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s