| one of the most frequently asked questions in SQL Server forums is how to format A datetime value or column into a specific date format. here's a summary of the different date formats that come standard in SQL Server as part of the convert function. following the standard date formats are some extended date formats that are often asked by SQL server developers. it is worth to note that the output of these date formats are of varchar data types already and not of datetime data type. with this in mind, any date comparisons completed MED after the datetime value has been formatted are using the varchar value of the date and time and not its original datetime value. |
| Standard date formats |
| Date Format |
Standard |
SQL statement |
Sample output |
Mon dd yyyy1
Hh: miam (or pm) |
Default |
Select convert (varchar (20), getdate (), 100) |
Jan 1 2005 pm1 |
| Mm/DD/yy |
USA |
Select convert (varchar (8), getdate (), 1) as [mm/DD/yy] |
11/23/98 |
| Mm/DD/YYYY |
USA |
Select convert (varchar (10), getdate (), 101) as [mm/DD/YYYY] |
11/23/1998 |
| YY. Mm. dd |
ANSI |
Select convert (varchar (8), getdate (), 2) as [yy. Mm. DD] |
72.01.01 |
| Yyyy. Mm. dd |
ANSI |
Select convert (varchar (10), getdate (), 102) as [YYYY. Mm. DD] |
1972.01.01 |
| Dd/mm/yy |
British/French |
Select convert (varchar (8), getdate (), 3) as [dd/mm/yy] |
19/02/72 |
| Dd/mm/yyyy |
British/French |
Select convert (varchar (10), getdate (), 103) as [DD/MM/YYYY] |
19/02/1972 |
| Dd. mm. yy |
German |
Select convert (varchar (8), getdate (), 4) as [dd. mm. yy] |
25.12.05 |
| Dd. mm. yyyy |
German |
Select convert (varchar (10), getdate (), 104) as [dd. mm. yyyy] |
25.12.2005 |
| DD-MM-YY |
Italian |
Select convert (varchar (8), getdate (), 5) as [DD-MM-YY] |
24-01-98 |
| DD-MM-YYYY |
Italian |
Select convert (varchar (10), getdate (), 105) as [DD-MM-YYYY] |
24-01-1998 |
| Dd mon YY1 |
- |
Select convert (varchar (9), getdate (), 6) as [DD mon YY] |
04 Jul 061 |
| Dd mon yyyy1 |
- |
Select convert (varchar (11), getdate (), 106) as [DD mon YYYY] |
04 Jul 20061 |
| Mon DD, YY1 |
- |
Select convert (varchar (10), getdate (), 7) as [Mon DD, YY] |
Jan 24, 981 |
| Mon DD, yyyy1 |
- |
Select convert (varchar (12), getdate (), 107) as [Mon DD, yyyy] |
Jan 24,199 81 |
| Hh: mm: SS |
- |
Select convert (varchar (8), getdate (), 108) |
03:24:53 |
| Mon dd yyyy hh: MI: SS: mmmam (or pm)1 |
Default +
Milliseconds |
Select convert (varchar (26), getdate (), 109) |
Apr 28 2006 12: 32: 29: 253pm1 |
| MM-DD-YY |
USA |
Select convert (varchar (8), getdate (), 10) as [MM-DD-YY] |
01-01-06 |
| MM-DD-YYYY |
USA |
Select convert (varchar (10), getdate (), 110) as [MM-DD-YYYY] |
01-01-2006 |
| YY/MM/dd |
- |
Select convert (varchar (8), getdate (), 11) as [yy/MM/DD] |
98/11/23 |
| Yyyy/mm/dd |
- |
Select convert (varchar (10), getdate (), 111) as [yyyy/mm/DD] |
1998/11/23 |
| Yymmdd |
ISO |
Select convert (varchar (6), getdate (), 12) as [yymmdd] |
980124 |
| Yyyymmdd |
ISO |
Select convert (varchar (8), getdate (), 112) as [yyyymmdd] |
19980124 |
| Dd mon yyyy hh: mm: SS: Mmm (24 h)1 |
Europe default + milliseconds |
Select convert (varchar (24), getdate (), 113) |
28 Apr 2006 00: 34: 55: 1901 |
| Hh: MI: SS: Mmm (24 h) |
- |
Select convert (varchar (12), getdate (), 114) as [hh: MI: SS: Mmm (24 h)] |
11: 34: 23: 013 |
| YYYY-MM-DD hh: MI: SS (24 h) |
ODBC canonical |
Select convert (varchar (19), getdate (), 120) |
13:42:24 |
| YYYY-MM-DD hh: MI: Ss. Mmm (24 hours) |
ODBC canonical
(With milliseconds) |
Select convert (varchar (23), getdate (), 121) |
1972-02-19 06:35:24. 489 |
| YYYY-MM-DDTHH: mm: SS: Mmm |
Iso8601 |
Select convert (varchar (23), getdate (), 126) |
1998-11-23t11: 25: 43: 250 |
| Dd mon yyyy hh: MI: SS: mmmam1 |
Kuwaiti |
Select convert (varchar (26), getdate (), 130) |
28 Apr 2006 12: 39: 32: 429am1 |
| Dd/mm/yyyy hh: MI: SS: mmmam |
Kuwaiti |
Select convert (varchar (25), getdate (), 131) |
28/04/2006 12: 39: 32: 429am |
|
Here are some more date formats that does not come standard in SQL Server as part ofConvertFunction.
| Extended date formats |
| Date Format |
SQL statement |
Sample output |
| YY-MM-DD |
Select substring (convert (varchar (10), getdate (), 120), 3, 8) as [YY-MM-DD] Select Replace (convert (varchar (8), getdate (), 11), '/', '-') as [YY-MM-DD] |
99-01-24 |
| YYYY-MM-DD |
Select convert (varchar (10), getdate (), 120) as [YYYY-MM-DD] Select Replace (convert (varchar (10), getdate (), 111 ),'/', '-') as [YYYY-MM-DD] |
2017-01-24 |
| Mm/yy |
Select right (convert (varchar (8), getdate (), 3), 5) as [mm/yy]
Select substring (convert (varchar (8), getdate (), 3), 4, 5) as [mm/yy] |
08/99 |
| Mm/yyyy |
Select right (convert (varchar (10), getdate (), 103), 7) as [mm/yyyy] |
12/2005 |
| YY/mm |
Select convert (varchar (5), getdate (), 11) as [yy/mm] |
99/08 |
| Yyyy/mm |
Select convert (varchar (7), getdate (), 111) as [yyyy/mm] |
2005/12 |
| Month DD, yyyy1 |
Select datename (mm, getdate () + right (convert (varchar (12), getdate (), 107), 9) as [month DD, yyyy] |
July 04,200 61 |
| Mon yyyy1 |
Select substring (convert (varchar (11), getdate (), 113), 4, 8) as [Mon YYYY] |
Apr 20061 |
| Month yyyy1 |
Select datename (mm, getdate () + ''+ Cast (Year (getdate () as varchar (4) as [month YYYY] |
February 20061 |
| Dd month1 |
Select cast (Day (getdate () as varchar (2) + ''+ datename (mm, getdate () as [DD month] |
11 September1 |
| Month dd1 |
Select datename (mm, getdate () + ''+ Cast (Day (getdate () as varchar (2) as [month DD] |
September 111 |
| Dd month yy1 |
Select cast (Day (getdate () as varchar (2) + ''+ datename (mm, getdate ()) + ''+ right (cast (Year (getdate () as varchar (4), 2) as [DD month yy] |
19 February 721 |
| Dd month yyyy1 |
Select cast (Day (getdate () as varchar (2) + ''+ datename (mm, getdate () +'' + Cast (Year (getdate ()) as varchar (4) as [DD month YYYY] |
11 September 20021 |
| MM-YY |
Select right (convert (varchar (8), getdate (), 5), 5) as [MM-YY]
Select substring (convert (varchar (8), getdate (), 5), 4, 5) as [MM-YY] |
12/92 |
| MM-YYYY |
Select right (convert (varchar (10), getdate (), 105), 7) as [MM-YYYY] |
05-2006 |
| YY-MM |
Select right (convert (varchar (7), getdate (), 120), 5) as [YY-MM]
Select substring (convert (varchar (10), getdate (), 120), 3, 5) as [YY-MM] |
92/12 |
| YYYY-MM |
Select convert (varchar (7), getdate (), 120) as [YYYY-MM] |
2006-05 |
| Mmddyy |
Select Replace (convert (varchar (10), getdate (), 1), '/', '') as [mmddyy] |
122506 |
| Mmddyyyy |
Select Replace (convert (varchar (10), getdate (), 101), '/', '') as [mmddyyyy] |
12252006 |
| Ddmmyy |
Select Replace (convert (varchar (10), getdate (), 3), '/', '') as [ddmmyy] |
240702 |
| Ddmmyyyy |
Select Replace (convert (varchar (10), getdate (), 103), '/', '') as [ddmmyyyy] |
24072002 |
| Mon-yy1 |
Select Replace (right (convert (varchar (9), getdate (), 6), 6), '', '-') as [Mon-yy] |
Sep-021 |
| Mon-yyyy1 |
Select Replace (right (convert (varchar (11), getdate (), 106), 8), '', '-') as [Mon-yyyy] |
Sep-20021 |
| DD-mon-yy1 |
Select Replace (convert (varchar (9), getdate (), 6), '', '-') as [DD-mon-yy] |
25-dec-051 |
| DD-mon-yyyy1 |
Select Replace (convert (varchar (11), getdate (), 106), '', '-') as [DD-mon-yyyy] |
25-dec-20051 |
1 To make the month name in upper case, simply use the upper string function. |
