Helpful SQL Server Date Functions

One of my common duties is to create and/or update reports in SSRS (SQL Server Reporting Services). A large number of these reports ran from very unclean data. To compensate for this, the original developers handled the data in a variety of inconsistent manners, mostly around dates. Although this may have gotten the job done originally, it does nothing for the quality of the report.

As these reports were updated, we began using standard date functions to improve development time and report quality.


/* First Day of the Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

/* Last Day of the Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0))

/* First Day of Next Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0)

/* Last Day of Next Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,2,GETDATE())),0))

/* First Day of Last Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())),0)

/* Last Day of Last Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

/* First Day of the Year */
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

/* Last Day of the Year */
SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,DATEADD(yy,1,GETDATE())),0))

/* First Day of the Quarter */
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)

/* Last Day of the Quarter */
SELECT DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,DATEADD(qq,1,GETDATE())),0))

/* First Monday of the Month */
SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,6 - DATEPART(DAY,GETDATE()),GETDATE())),0)

~Ron

Advertisements

0 Responses to “Helpful SQL Server Date Functions”



  1. Leave a Comment

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: