I can’t give you a full tutorial on all things Dates when it comes to SQL, but I can show you how I solved a date task for a project I am now finishing up. This project includes finding data between these dates:

First Day of Last Month
Current Day of Last Month - so today’s day - 28 - but of last month
Last Day of Last Month

First Day of Month
Current Day of Month - Today
Last Day of Month

First Day of Next Month
Current Day of Next Month - same, today’s day of the month for next month
Last Day of Next Month

Oh, I also needed to get this month, for last year with the three same time frames. First day of the current month of last year, last day of current month of last year, and today’s day of last year.

First off, it’s relatively easy to search the interweb and use the first thing that pops up and gives you the answer. But when I tried that, the code was long, ugly, and just wasn’t to easy to maintain. So, I started with the most simple of all:


SELECT convert(varchar,DATEADD(YEAR,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)),101),'First Day of This Month Last Year'
UNION
SELECT convert(varchar,DATEADD(YEAR,-1,DATEADD(MONTH,0,getdate())),101),'Today Last Year'
UNION
SELECT convert(varchar,DATEADD(YEAR,-1,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),-1)),101),'Last Day of This Month Last Year'
UNION
SELECT convert(varchar,DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)),101),'First Day of Last Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,-1,getdate()),101),'This Time Last Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),-1),101),'Last Day of Last Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0),101),'First Day of This Month'
UNION
SELECT convert(varchar,getdate(),101),'Today'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),-1),101),'Last Day of This Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),0),101),'First Day of Next Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,+1,getdate()),101),'This Time Next Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,+1,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),-1)),101),'Last Day of Next Month'


Results:

01/01/2007 First Day of This Month Last Year
01/29/2007 Today Last Year
01/31/2007 Last Day of This Month Last Year
12/01/2007 First Day of Last Month
12/29/2007 This Time Last Month
12/31/2007 Last Day of Last Month
01/01/2008 First Day of This Month
01/29/2008 Today
01/31/2008 Last Day of This Month
02/01/2008 First Day of Next Month
02/29/2008 This Time Next Month
02/29/2008 Last Day of Next Month


I hope many will find this useful. Of course, you’ll most likely want to set variables with these…as your SQL could get very long and unreadable if you had to copy and paste these over and over for BETWEENs or some other similar date range.

** Please feel free to post…I’d like to know what others do, if there are more efficient ways, or newer ways. I am a relative Noob at SQL, so I know this can be done better.