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:
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 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?
Like this? Follow me ↯
Tweet Follow @pfreitagDateFormat 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
Comments
Another way you could use an integer would be CONVERT(int, CONVERT(char(8), getdate(), 112)) but I suspect that Roland's method performs better.
@Andy - yes that's another great use for this technique.
http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/
So this: SELECT CONVERT(INT, CONVERT(DATETIME, '01/01/2009 11:59:00'))
SELECT CONVERT(INT, CONVERT(DATETIME, '01/01/2009 12:01:00'))
Returns:
39812
39813
Try this...
SELECT
CONVERT(VARCHAR(10), YourFieldName, 111) AS YourAliasName
FROM YourTableName
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