pf » DateFormat for SQL Server

Have you ever tried to find a DateFormat or date_format function in SQL Server? Chances are you probably ended up concatenating a bunch of DatePart strings to get the format you are looking for.

What about grouping by a date in SQL Server? This can be a pain because the datetime and the smalldatetime types both contain the time of day, so you can't simply add a datetime to the GROUP BY clause, because it will group by the time, not the date.

I have been working with SQL Server since version 7, and just today I found a good way to do this!

It turns out there is a third argument to the CONVERT function in SQL server that accepts a style (accepts an integer). If you pass in a 101 it will return the date in mm/dd/yyyy format.

So for example let's say you want to get the revenue for a given day you would do something like this:

SELECT SUM(PricePaid) AS Revenue, CONVERT(char(10), DateOrdered, 101) AS DateOrdered
FROM Orders
GROUP BY CONVERT(char(10), DateOrdered, 101)

Here are a few style values you can use:

Find a full list in the CONVERT docs.

This still isn't the prettiest solution, but it's much better than using DatePart. PS - Microsoft, why not give us a real DateFormat function?