By Pete Freitag
Expert Author
Article Date: 2008-12-09
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:
| Style | CONVERT Format Mask |
|---|
| 0 | mon dd yyyy hh:miAM (this is the default style) |
| 1 | mm/dd/yy |
| 101 | mm/dd/yyyy |
| 112 | yyyymmdd |
| 126 | yyyy-mm-ddThh:mi:ss.mmm (ISO8601 format) |
Find a full list in the CONVERTdocs.
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?
Comments
