Wednesday, 12 May 2010

TSQL - Grouping data by year and month

A useful way to group data in result sets using only one date field is often to employ a YYYY-MM or YYYY-MM-DD format.

In the past I'd opted for a slightly painful way of taking the dateparts of the year and month, converting them to varchars and then concatinating them together with the hyphens. The following are examples use getdate(), but obviously you'd use your own field.

SELECT CAST(DATEPART(YYYY, GETDATE()) as CHAR(4)) + '-' + REPLICATE('0', 2-LEN(CAST(DATEPART(M, GETDATE()) as VARCHAR(2)))) + CAST(DATEPART(M, GETDATE()) as VARCHAR(2))

Not very pretty I think you'll agree. Especially if you want to immediately use it in a group by. The following is much more elegant and returns the same result!
SELECT CONVERT(CHAR(7),GETDATE(),23)

If you want to include the day part as well then use all 10 characters returned:
SELECT CONVERT(CHAR(10),GETDATE(),23)

For more formats have a look at http://msdn.microsoft.com/en-us/library/ms187928.aspx