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?
Comments
You can also use GROUP BY FLOOR(CONVERT(FLOAT, DateOrdered)), which does integer sorting instead of character sorting.
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.
@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.
@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.
Sorry typo... in my last comment it returns number of days since 1900
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/
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
@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
@Tshepo Try this... SELECT CONVERT(VARCHAR(10), YourFieldName, 111) AS YourAliasName FROM YourTableName
Now there is a FormatDate function as well as other formatting functions for SQL Server 2005 and SQL Server 2008 at www.westclintech.com
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?
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
Hi I want to convert my date into ddMONyyyy(19APR2011) format in sqlserver 2008.can anyone please help me. Thanks