Pete Freitag Pete Freitag

DateFormat for SQL Server

Published on December 05, 2008
By Pete Freitag

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?

sql sql server microsoft date dateformat

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:

Discuss / Follow me on Twitter ↯


You can also use GROUP BY FLOOR(CONVERT(FLOAT, DateOrdered)), which does integer sorting instead of character sorting.
by Roland Collins on 12/05/2008 at 12:59:04 PM UTC
This is also sometimes necessary when filtering dates using BETWEEN on SQL Server if your column is a DATETIME and you do not want the time taken into consideration in your filter.
by Andy Sandefer on 12/05/2008 at 1:06:32 PM UTC
@Roland - that's a neat trick, it appears that CONVERT(float, somedate) returns the number of days since 1/1/1900 as the natural number with the decimal part representing time. I haven't seen that documented anywhere, nice!

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.
by Pete Freitag on 12/05/2008 at 1:41:23 PM UTC
@Roaland actually it appears that you can just use CONVERT(int, getdate()) which will return the number of days since 1970, so no need to use floor.
by Pete Freitag on 12/05/2008 at 1:44:19 PM UTC
Sorry typo... in my last comment it returns number of days since 1900
by Pete Freitag on 12/05/2008 at 1:45:18 PM UTC
This UDF is pretty slick and keeps things clean.
by Jason on 12/05/2008 at 2:21:28 PM UTC
I actually think T-SQL is really awesome when dealing with dates. I will make a blog post using some of the cool features.
by John Gag on 12/05/2008 at 2:41:59 PM UTC
@Pete - The only issue with converting to an int directly is that you get different values for before noon and after noon due to rounding.

So this: SELECT CONVERT(INT, CONVERT(DATETIME, '01/01/2009 11:59:00'))



by Roland Collins on 12/23/2008 at 3:16:28 PM UTC
Try this...
CONVERT(VARCHAR(10), YourFieldName, 111) AS YourAliasName
FROM YourTableName
by Andy Sandefer on 05/07/2009 at 3:49:53 AM UTC
Now there is a FormatDate function as well as other formatting functions for SQL Server 2005 and SQL Server 2008 at
by CharlieF on 06/04/2009 at 9:29:29 AM UTC
why would I be getting an "invalid identifier" when I run your sql examples?

in this statement:


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?
by Lavanya on 07/07/2010 at 6:38:40 PM UTC
trying to add 30 business days to current date. if end date [current date +30 ]falls on a saturday, end date should be the next monday.

can anyone help with this query in s
by RK on 12/13/2010 at 2:35:38 PM UTC
I want to convert my date into ddMONyyyy(19APR2011) format in sqlserver 2008.can anyone please help me. Thanks
by ship on 04/20/2011 at 1:35:54 AM UTC