DateFormat for SQL Server
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:
|Style||CONVERT Format Mask|
|0||mon dd yyyy hh:miAM (this is the default style)|
|126||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
Like this? Follow me ↯Tweet Follow @pfreitag
You might also like:
- Use varchar(max) instead of text in SQL Server - December 9, 2009
- Cheat Sheet for SQL Server - April 20, 2009
- Dear SQL Server Enterprise Manager Developer - July 31, 2008
- Try Catch for SQLServer T-SQL - April 7, 2008
in this statement:
SELECT CONVERT(INT, CONVERT(DATETIME, '01/01/2009 12:01:00'))
what does "datetime" represent? is it a date in presisely the format given? could someone fix this post so that it has an example that works?
can anyone help with this query in s
I want to convert my date into ddMONyyyy(19APR2011) format in sqlserver 2008.can anyone please help me. Thanks