DateFormat for SQL Server

databases

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?

Trackbacks

Trackback Address: 687/E970E2F4F55447F195F05E117B74D7A9

Comments

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

On 12/05/2008 at 1:06:32 PM UTC Andy Sandefer wrote:
2
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 1:41:23 PM UTC Pete Freitag wrote:
3
@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 1:44:19 PM UTC Pete Freitag wrote:
4
@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 1:45:18 PM UTC Pete Freitag wrote:
5
Sorry typo... in my last comment it returns number of days since 1900

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

http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/

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

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

SELECT CONVERT(INT, CONVERT(DATETIME, '01/01/2009 12:01:00'))

Returns:

39812 39813

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

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

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

On 07/07/2010 at 6:38:40 PM UTC Lavanya wrote:
12
why would I be getting an "invalid identifier" when I run your sql examples?

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?

On 12/13/2010 at 2:35:38 PM UTC RK wrote:
13
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 2:36:05 PM UTC RK wrote:
14
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 1:35:54 AM UTC ship wrote:
15
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 5:17:41 AM UTC Deane wrote:
16
Here's the proper format syntax.. SELECT CONVERT(VARCHAR(15),Date_Today,101) AS Date FROM Table1

On 02/25/2012 at 9:16:05 PM UTC illurviff wrote:
17
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



foundeo


did you hack my cf?