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?

Related Entries

11 people found this page useful, what do you think?


Trackback Address: 687/E970E2F4F55447F195F05E117B74D7A9


On 12/05/2008 at 2:59:04 PM EST Roland Collins wrote:
You can also use GROUP BY FLOOR(CONVERT(FLOAT, DateOrdered)), which does integer sorting instead of character sorting.

On 12/05/2008 at 3:06:32 PM EST Andy Sandefer wrote:
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.

On 12/05/2008 at 3:41:23 PM EST Pete Freitag wrote:
@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.

On 12/05/2008 at 3:44:19 PM EST Pete Freitag wrote:
@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.

On 12/05/2008 at 3:45:18 PM EST Pete Freitag wrote:
Sorry typo... in my last comment it returns number of days since 1900

On 12/05/2008 at 4:21:28 PM EST Jason wrote:
This UDF is pretty slick and keeps things clean.

On 12/05/2008 at 4:41:59 PM EST John Gag wrote:
I actually think T-SQL is really awesome when dealing with dates. I will make a blog post using some of the cool features.

On 12/23/2008 at 5:16:28 PM EST Roland Collins wrote:
@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'))



39812 39813

On 05/07/2009 at 5:13:09 AM EDT Tshepo wrote:
How to convert date format to yyyy/MM/DD?

On 05/07/2009 at 5:49:53 AM EDT Andy Sandefer wrote:
@Tshepo Try this... SELECT CONVERT(VARCHAR(10), YourFieldName, 111) AS YourAliasName FROM YourTableName

On 06/04/2009 at 11:29:29 AM EDT CharlieF wrote:
Now there is a FormatDate function as well as other formatting functions for SQL Server 2005 and SQL Server 2008 at

On 07/07/2010 at 8:38:40 PM EDT Lavanya wrote:
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?

On 12/13/2010 at 4:35:38 PM EST RK wrote:
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

On 12/13/2010 at 4:36:05 PM EST RK wrote:
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 ms sql server

On 04/20/2011 at 3:35:54 AM EDT ship wrote:
Hi I want to convert my date into ddMONyyyy(19APR2011) format in sqlserver 2008.can anyone please help me. Thanks

On 05/02/2011 at 7:17:41 AM EDT Deane wrote:
Here's the proper format syntax.. SELECT CONVERT(VARCHAR(15),Date_Today,101) AS Date FROM Table1

On 02/25/2012 at 11:16:05 PM EST illurviff wrote:
Hi nto All Please, give me some links on the popular dating sites. Need some links on dating catalog. Thanks. illurviff

Post a Comment


Spell Checker by Foundeo

Recent Entries


did you hack my cf?