Everyday we are dealing with dates. Daily reports, monthly sales, annual projections, etc. Here are few tips how to get dates you want from a Microsoft SQL Database (tested on SQL 2005 Server).

The following retrieves today’s date (now):

SELECT getdate()  -- result: 2009-05-05 11:32:45.343
SELECT dbo.today() -- result: 2009-05-05 00:00:00.000
SELECT year(getdate()) -- result: 2009
SELECT month(getdate()) -- result: 5


Do you want a monhtly sales report? Here it is:

SELECT year(sales_date) as 'Year, month(sales_date) as 'Month', sum(sales_qty) as 'Sales'
FROM sales
GROUP BY year(sales_date), month(sales_date)
ORDER BY year(sales_date), month(sales_date)


The result is going to look like this:

Year          Month          Sales
------       --------       ------
2009          3             10.00
2009          4             14.00
2009          5              7.00

and so on…

What if you want to get the last date of the month? No problem:

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) LastDay_PreviousMonth
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) LastDay_CurrentMonth
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) LastDay_NextMonth

and also the first date of the month (although it’s obvious it is only ‘1′):

SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) FirstDay_PreviousMonth
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) FirstDay_CurrentMonth
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) FirstDay_NextMonth


Obviously I can’t cover every case here. One place to look is obviously MSDN Library.

Click here for SQL 2005 Server and here for SQL 2008 Server.