| |
| Date
Formats |
|
|
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 performed 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 YYYY 1
HH:MIAM (or PM) |
Default |
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) AS [MON D YYYY HH:MM(AM/PM)]
SELECT FORMAT(GETDATE(), 'MMM d yyyy hh:mmtt') AS [MON D YYYY HH:MM(AM/PM)]; |
Sep 2 2024 12:34PM 1 |
| MM/DD/YY |
USA |
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
SELECT FORMAT(GETDATE(), 'MM/dd/yy') AS [MM/DD/YY]
|
09/02/24 |
| MM/DD/YYYY |
USA |
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy') AS [MM/DD/YYYY]
|
09/02/2024 |
| YY.MM.DD |
ANSI |
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
SELECT FORMAT(GETDATE(), 'yy.MM.dd') AS [YY.MM.DD]
|
24.09.02 |
| YYYY.MM.DD |
ANSI |
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
SELECT FORMAT(GETDATE(), 'yyyy.MM.dd') AS [YYYY.MM.DD]
|
2024.09.02 |
| DD/MM/YY |
British/French |
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
SELECT FORMAT(GETDATE(), 'dd/MM/yy') AS [DD/MM/YY] |
02/09/24 |
| DD/MM/YYYY |
British/French |
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS [DD/MM/YYYY] |
02/09/2024 |
| DD.MM.YY |
German |
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
SELECT FORMAT(GETDATE(), 'dd.MM.yy') AS [DD.MM.YY] |
02.09.24 |
| DD.MM.YYYY |
German |
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
SELECT FORMAT(GETDATE(), 'dd.MM.yyyy') AS [DD.MM.YYYY] |
02.09.2024 |
| DD-MM-YY |
Italian |
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
SELECT FORMAT(GETDATE(), 'dd-MM-yy') AS [DD-MM-YY] |
02-09-24 |
| DD-MM-YYYY |
Italian |
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
SELECT FORMAT(GETDATE(), 'dd-MM-yyyy') AS [DD-MM-YYYY] |
02-09-2024 |
| DD Mon YY 1 |
- |
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD Mon YY]
SELECT FORMAT(GETDATE(), 'dd MMM yy') AS [DD Mon YY] |
02 Sep 24 1 |
| DD Mon YYYY 1 |
- |
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD Mon YYYY]
SELECT FORMAT(GETDATE(), 'dd MMM yyyy') AS [DD Mon YYYY] |
02 Sep 2024 1 |
| Mon DD, YY 1 |
- |
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
SELECT FORMAT(GETDATE(), 'MMM dd, yy') AS [Mon DD, YY] |
Sep 02, 24 1 |
| Mon DD, YYYY 1 |
- |
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
SELECT FORMAT(GETDATE(), 'MMM dd, yyyy') AS [Mon DD, YYYY] |
Sep 02, 2024 1 |
| HH:MM:SS |
- |
SELECT CONVERT(VARCHAR(8), GETDATE(), 108) AS [HH:MM:SS]
SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS [HH:MM:SS] |
12:34:56 |
| Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 |
Default +
milliseconds |
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) AS [MON D YYYY HH:MM:SS:MS(AM/PM)]
SELECT FORMAT(GETDATE(), 'MMM d yyyy hh:mm:ss:ffftt') AS [MON D YYYY HH:MM:SS:MS(AM/PM)] |
Sep 2 2024 12:34:56:789PM 1 |
| MM-DD-YY |
USA |
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
SELECT FORMAT(GETDATE(), 'MM-dd-yy') AS [MM-DD-YY] |
09-02-24 |
| MM-DD-YYYY |
USA |
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
SELECT FORMAT(GETDATE(), 'MM-dd-yyyy') AS [MM-DD-YYYY] |
09-02-2024 |
| YY/MM/DD |
- |
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
SELECT FORMAT(GETDATE(), 'yy/MM/dd') AS [YY/MM/DD] |
24/09/02 |
| YYYY/MM/DD |
- |
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
SELECT FORMAT(GETDATE(), 'yyyy/MM/dd') AS [YYYY/MM/DD] |
2024/09/02 |
| YYMMDD |
ISO |
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
SELECT FORMAT(GETDATE(), 'yyMMdd') AS [YYMMDD] |
240902 |
| YYYYMMDD |
ISO |
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
SELECT FORMAT(GETDATE(), 'yyyyMMdd') AS [YYYYMMDD] |
20240902 |
| DD Mon YYYY HH:MM:SS:MMM(24h) 1 |
Europe default + milliseconds |
SELECT CONVERT(VARCHAR(24), GETDATE(), 113) AS [DD MON YYYY HH:MM:SS:MS]
SELECT FORMAT(GETDATE(), 'dd MMM yyyy HH:mm:ss:fff') AS [DD MON YYYY HH:MM:SS:MS] |
02 Sep 2024 12:34:56:789 1 |
| HH:MI:SS:MMM(24H) |
- |
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]
SELECT FORMAT(GETDATE(), 'HH:mm:ss:fff') AS [HH:MI:SS:MMM(24H)] |
12:34:56:789 |
| YYYY-MM-DD HH:MI:SS(24h) |
ODBC Canonical |
SELECT CONVERT(VARCHAR(19), GETDATE(), 120) AS [YYYY-MM-DD HH:MI:SS(24h)]
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS [YYYY-MM-DD HH:MI:SS(24h)] |
2024-09-02 12:34:56 |
| YYYY-MM-DD HH:MI:SS.MMM(24h) |
ODBC Canonical
(with milliseconds) |
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) AS [YYYY-MM-DD HH:MI:SS.MMM(24h)]
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff') AS [YYYY-MM-DD HH:MI:SS.MMM(24h)] |
2024-09-02 12:34:56.789 |
| YYYY-MM-DDTHH:MM:SS.MMM |
ISO8601 |
SELECT CONVERT(VARCHAR(23), GETDATE(), 126) AS [YYYY-MM-DDTHH:MM:SS.MS]
SELECT FORMAT(GETDATE(), 'yyyy-MM-ddTHH:mm:ss.fff') AS [YYYY-MM-DDTHH:MM:SS.MS] |
2024-09-02T12:34:56.789 |
| DD Mon YYYY HH:MI:SS:MMMAM 1 |
Kuwaiti |
SELECT CONVERT(VARCHAR(26), GETDATE(), 130) |
02 Sep 2024 12:34:56:789PM 1 |
| DD/MM/YYYY HH:MI:SS:MMMAM |
Kuwaiti |
SELECT CONVERT(VARCHAR(25), GETDATE(), 131) |
02/09/2024 12:34:56:789PM |
|
|
Here's a script that will generate the different standard date formats using the CONVERT conversion function where the output does not include the century.
|
|
|
|
Here's a script that will generate the different standard date formats using the CONVERT conversion function where the output includes the century.
|
|
Here are some more date formats that does not come standard in SQL Server as
part of the CONVERT function.
| 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]
SELECT FORMAT(GETDATE(), 'yy-MM-dd') AS [YY-MM-DD]
|
24-09-02 |
| YYYY-MM-DD |
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS [YYYY-MM-DD]
|
2024-09-02 |
| 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]
SELECT FORMAT(GETDATE(), 'MM/yy') AS [MM/YY]
|
09/24 |
| MM/YYYY |
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]
SELECT FORMAT(GETDATE(), 'MM/yyyy') AS [MM/YYYY]
|
09/2024 |
| YY/MM |
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]
SELECT FORMAT(GETDATE(), 'yy/MM') AS [YY/MM]
|
24/09 |
| YYYY/MM |
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]
SELECT FORMAT(GETDATE(), 'yyyy/MM') AS [YYYY/MM]
|
2024/09 |
| Month DD, YYYY 1 |
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]
SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [Month DD, YYYY]
|
September 02, 2024 1 |
| Mon YYYY 1 |
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]
SELECT FORMAT(GETDATE(), 'MMM yyyy') AS [Mon YYYY]
|
Sep 2024 1 |
| Month YYYY 1 |
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]
SELECT FORMAT(GETDATE(), 'MMMM yyyy') AS [Month YYYY]
|
September 2024 1 |
| DD Month 1 |
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]
SELECT FORMAT(GETDATE(), 'dd MMMM') AS [DD Month]
|
02 September 1 |
| Month DD 1 |
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]
SELECT FORMAT(GETDATE(), 'MMMM dd') AS [Month DD]
|
September 02 1 |
| DD Month YY 1 |
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]
SELECT FORMAT(GETDATE(), 'dd MMMM yy') AS [DD Month YY]
|
02 September 24 1 |
| DD Month YYYY 1 |
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]
SELECT FORMAT(GETDATE(), 'dd MMMM yyyy') AS [DD Month YYYY]
|
02 September 2024 1 |
| 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]
SELECT FORMAT(GETDATE(), 'MM-yy') AS [MM-YY]
|
09-24 |
| MM-YYYY |
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]
SELECT FORMAT(GETDATE(), 'MM-yyyy') AS [MM-YYYY] |
09-2024 |
| 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]
SELECT FORMAT(GETDATE(), 'yy-MM') AS [YY-MM]
|
24-09 |
| YYYY-MM |
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]
SELECT FORMAT(GETDATE(), 'yyyy-MM') AS [YYYY-MM]
|
2024-09 |
| MMDDYY |
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]
SELECT FORMAT(GETDATE(), 'MMddyy') AS [MMDDYY]
|
090224 |
| MMDDYYYY |
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]
SELECT FORMAT(GETDATE(), 'MMddyyyy') AS [MMDDYYYY]
|
09022024 |
| DDMMYY |
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]
SELECT FORMAT(GETDATE(), 'ddMMyy') AS [DDMMYY]
|
020924 |
| DDMMYYYY |
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]
SELECT FORMAT(GETDATE(), 'ddMMyyyy') AS [DDMMYYYY]
|
02092024 |
| Mon-YY 1 |
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]
SELECT FORMAT(GETDATE(), 'MMM-yy') AS [Mon-YY]
|
Sep-24 1 |
| Mon-YYYY 1 |
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]
SELECT FORMAT(GETDATE(), 'MMM-yyyy') AS [Mon-YYYY]
|
Sep-2024 1 |
| DD-Mon-YY 1 |
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]
SELECT FORMAT(GETDATE(), 'dd-MMM-yy') AS [DD-Mon-YY]
|
02-Sep-24 1 |
| DD-Mon-YYYY 1 |
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]
SELECT FORMAT(GETDATE(), 'dd-MMM-yyyy') AS [DD-Mon-YYYY]
|
02-Sep-2024 1 |
1 To make the month name in upper case, simply use
the UPPER string function.
|
|
Related Topics:
|
|
|
|
|
|