Over on Experts Exchange I saw a question that I get all the time, “How do I format this DATETIME like x?” If they’re asking to learn, I point them over to the CAST and CONVERT article on msdn. If you wish to change a Microsoft SQL DATETIME column from the standard format 2008-01-28 12:43:13.210 to any other format you need to use CONVERT. CONVERT will let you choose any option from the following table as your formatting option.
| Without century (yy) (1) | With century (yyyy) | Standard | Input/Output (3) |
|---|---|---|---|
| - | 0 or 100 (1,2) | Default | mon dd yyyy hh:miAM (or PM) |
| 1 | 101 | U.S. | mm/dd/yyyy |
| 2 | 102 | ANSI | yy.mm.dd |
| 3 | 103 | British/French | dd/mm/yyyy |
| 4 | 104 | German | dd.mm.yy |
| 5 | 105 | Italian | dd-mm-yy |
| 6 | 106(1) | - | dd mon yy |
| 7 | 107(1) | - | Mon dd, yy |
| 8 | 108 | - | hh:mi:ss |
| - | 9 or 109 (1,2) | Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 | 110 | USA | mm-dd-yy |
| 11 | 111 | JAPAN | yy/mm/dd |
| 12 | 112 | ISO | yymmdd yyyymmdd |
| - | 13 or 113 (1,2) | Europe default + milliseconds | dd mon yyyy hh:mi:ss:mmm(24h) |
| 14 | 114 | - | hh:mi:ss:mmm(24h) |
| - | 20 or 120 (2) | ODBC canonical | yyyy-mm-dd hh:mi:ss(24h) |
| - | 21 or 121 (2) | ODBC canonical (with milliseconds) | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| - | 126 (4) | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (no spaces) |
| - | 127(6, 7) | ISO8601 with time zone Z. | yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) |
| - | 130 (1,2) | Hijri (5) | dd mon yyyy hh:mi:ss:mmmAM |
| - | 131 (2) | Hijri (5) | dd/mm/yy hh:mi:ss:mmmAM |
In order to use the formatting, simply use CONVERT(VARCHAR(length), dateColumn, formatOption). I’ve included a few examples below.
declare @ExampleDateTime datetime set @ExampleDateTime = '2008-01-28 12:43:13.210' SELECT CONVERT(varchar(10), @exampleDateTime, 101) AS Result Result ------ 01/28/2008 SELECT CONVERT(varchar(26), @exampleDateTime, 9) AS Result Result -------------------------- Jan 28 2008 12:43:13:210PM SELECT CONVERT(varchar(20), @exampleDateTime) AS Result Result ------------------- Jan 28 2008 12:43PM
While there was quite a few people offering assistance on how to use the CONVERT function I hope you will add a bookmark to the Microsoft Books Online article I’ve referenced. It will save you a ton of time when you have to format your dates a certain way. If you have to write reports, CONVERT will become a common site in your T-SQL code.
If you have any questions about how to accomplish your goals in SQL, please send on your questions. I’m here to help!
References
