DateFormat for SQL Server
By Pete Freitag
Have you ever tried to find a
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:
|CONVERT Format Mask
|mon dd yyyy hh:miAM (this is the default style)
|yyyy-mm-ddThh:mi:ss.mmm (ISO8601 format)
Find a full list in the
This still isn't the prettiest solution, but it's much better than using
DatePart. PS - Microsoft, why not give us a real
DateFormat for SQL Server was first published on December 05, 2008.
If you like reading about sql, sql server, microsoft, date, or dateformat then you might also like:
- Use varchar(max) instead of text in SQL Server
- Cheat Sheet for SQL Server
- Dear SQL Server Enterprise Manager Developer
- Try Catch for SQLServer T-SQL