Friday, 10 December 2010

Back in the blog

It's winter and I've finally managed to resurrect my blog, which was off-line for a bit while I was changing providers. I try not to blog anything too useless so I won't be putting loads of content up, but hopefully something of benefit to someone.

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