Simon Worth’s SQL Server Weblog

SQL Server and its many complexities

Getting Date Information

leave a comment »

–Get the week day of the last day of the current year
SELECT DATENAME(dw, DATEADD(yy, DATEDIFF(yy, 0, GETDATE())+1, -1))

–Get the week day of the last day of the previous year
SELECT DATENAME(dw, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), -1))

–First day of the current month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

–First week day of the current month
SELECT DATENAME(dw, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

–Monday of the current week
SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)

–First day of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

–Midnight of the current day
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

–Last day of the previous month
SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

–Last day of the previous year
SELECT DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))

–Last day of the current month
SELECT DATEADD(MS,-3,DATEADD(MM, DATEDIFF(M,0,GETDATE())+1, 0))

–Last day of the current year
SELECT DATEADD(YY, DATEDIFF(YY, 0, GETDATE())+1, -1)

–First Monday of the current month
SELECT DATEADD(WK, DATEDIFF(WK, 0, DATEADD(DD, 6-DATEPART(DAY, GETDATE()), GETDATE())), 0)

Advertisements

Written by Simon Worth

January 6, 2009 at 12:25 pm

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

%d bloggers like this: