|
|
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.
|
The SQL statements used below to return the different date formats use the SYSDATETIME() date function, which is new to SQL Server 2008. The
SYSDATETIME() function returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The SYSDATETIME()
function used below can be replaced by the GETDATE() or GETUTCDATE() functions. The results will be the same unless the date format includes the
nanosecond portion of the time.
To make the date format results consistent, the date and time used to generate the sample output is June 8, 2011 1:30:45.9428675 PM.
| Date Format |
Standard |
SQL Statement |
Sample Output |
Mon DD YYYY 1
HH:MIAM (or PM) |
Default |
SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100) AS [Mon DD YYYY HH:MIAM (or PM)]
SELECT FORMAT(SYSDATETIME(), 'MMM dd yyyy hh:mmtt') AS [Mon DD YYYY HH:MIAM (or PM)] |
Sep 13 2024 1:23AM 1 |
| MM/DD/YY |
USA |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]
SELECT FORMAT(SYSDATETIME(), 'MM/dd/yy') AS [MM/DD/YY]
|
09/13/24 |
| MM/DD/YYYY |
USA |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]
SELECT FORMAT(SYSDATETIME(), 'MM/dd/yyyy') AS [MM/DD/YYYY] |
09/13/2024 |
| YY.MM.DD |
ANSI |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]
SELECT FORMAT(SYSDATETIME(), 'yy.MM.dd') AS [YY.MM.DD] |
24.09.13 |
| YYYY.MM.DD |
ANSI |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]
SELECT FORMAT(SYSDATETIME(), 'yyyy.MM.dd') AS [YYYY.MM.DD] |
2024.09.13 |
| DD/MM/YY |
British/French |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]
SELECT FORMAT(SYSDATETIME(), 'dd/MM/yy') AS [DD/MM/YY] |
13/09/24 |
| DD/MM/YYYY |
British/French |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]
SELECT FORMAT(SYSDATETIME(), 'dd/MM/yyyy') AS [DD/MM/YYYY] |
13/09/2024 |
| DD.MM.YY |
German |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]
SELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY] |
13.09.24 |
| DD.MM.YYYY |
German |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]
SELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY] |
13.09.2024 |
| DD-MM-YY |
Italian |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]
SELECT FORMAT(SYSDATETIME(), 'dd-MM-yy') AS [DD-MM-YY] |
13-09-24 |
| DD-MM-YYYY |
Italian |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]
SELECT FORMAT(SYSDATETIME(), 'dd-MM-yyyy') AS [DD-MM-YYYY] |
13-09-2024 |
| DD Mon YY 1 |
- |
SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD Mon YY]
SELECT FORMAT(SYSDATETIME(), 'dd MMM yy') AS [DD Mon YY] |
13 Sep 24 1 |
| DD Mon YYYY 1 |
- |
SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD Mon YYYY]
SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy') AS [DD Mon YYYY] |
13 Sep 2024 1 |
| Mon DD, YY 1 |
- |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]
SELECT FORMAT(SYSDATETIME(), 'MMM dd, yy') AS [Mon DD, YY] |
Sep 13, 24 1 |
| Mon DD, YYYY 1 |
- |
SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]
SELECT FORMAT(SYSDATETIME(), 'MMM dd, yyyy') AS [Mon DD, YYYY] |
Sep 13, 2024 1 |
| HH:MM:SS |
- |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8) AS [HH:MM:SS]
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108) AS [HH:MM:SS]
SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss') AS [HH:MM:SS] |
01:23:45 |
| Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1 |
Default +
nanoseconds |
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109) |
Jun 8 2011 1:30:45.9428675PM 1 |
| MM-DD-YY |
USA |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]
SELECT FORMAT(SYSDATETIME(), 'MM-dd-yy') AS [MM-DD-YY] |
09-13-24 |
| MM-DD-YYYY |
USA |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]
SELECT FORMAT(SYSDATETIME(), 'MM-dd-yyyy') AS [MM-DD-YYYY] |
09-13-2024 |
| YY/MM/DD |
- |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]
SELECT FORMAT(SYSDATETIME(), 'yy/MM/dd') AS [YY/MM/DD] |
24/09/13 |
| YYYY/MM/DD |
- |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]
SELECT FORMAT(SYSDATETIME(), 'yyyy/MM/dd') AS [YYYY/MM/DD] |
2024/09/13 |
| YYMMDD |
ISO |
SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]
SELECT FORMAT(SYSDATETIME(), 'yyMMdd') AS [YYMMDD] |
240913 |
| YYYYMMDD |
ISO |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]
SELECT FORMAT(SYSDATETIME(), 'yyyyMMdd') AS [YYYYMMDD] |
20240913 |
| DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1 |
Europe default + nanoseconds |
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13) AS [DD Mon YYYY HH:MM:SS.NNNNNNN]
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113) AS [DD Mon YYYY HH:MM:SS.NNNNNNN]
SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy HH:mm:ss.fffffff') AS [DD Mon YYYY HH:MM:SS.NNNNNNN] |
13 Sep 2024 01:23:45.67890121 |
| HH:MI:SS.NNNNNNN(24H) |
- |
SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)]
SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)]
SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss.ffffff') AS [HH:MI:SS:MMM(24H)] |
01:23:45.6789012 |
| YYYY-MM-DD HH:MI:SS(24h) |
ODBC Canonical |
SELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120) AS [YYYY-MM-DD HH:MI:SS(24h)]
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss') AS [YYYY-MM-DD HH:MI:SS(24h)] |
2024-09-13 01:23:45 |
| YYYY-MM-DD HH:MI:SS.NNNNNNN(24h) |
ODBC Canonical
(with nanoseconds) |
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 121) AS [YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)]
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss.fffffff') AS [YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)] |
2024-09-13 01:23:45.6789012 |
| MM/DD/YY HH:MI:SS AM |
- |
SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22) AS [MM/DD/YY HH:MI:SS AM]
SELECT FORMAT(SYSDATETIME(), 'MM/dd/yy hh:mm:ss tt') AS [MM/DD/YY HH:MI:SS AM] |
09/13/24 1:23:45 AM |
| YYYY-MM-DD |
- |
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23) AS [YYYY-MM-DD]
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd') AS [YYYY-MM-DD] |
2024-09-131 |
| HH:MI:SS (24h) |
- |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24) AS [HH:MI:SS (24h)]
SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss') AS [HH:MI:SS (24h)] |
01:23:45 |
| YYYY-MM-DD HH:MI:SS.NNNNNNN |
- |
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 25) AS [YYYY-MM-DD HH:MI:SS.NNNNNNN]
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss.fffffff') AS [YYYY-MM-DD HH:MI:SS.NNNNNNN] |
2024-09-13 01:23:45.67890121 |
| YYYY-MM-DDTHH:MM:SS.NNNNNNN |
ISO8601 |
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 126) AS [YYYY-MM-DDTHH:MM:SS.NNNNNNN]
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-ddTHH:mm:ss.fffffff') AS [YYYY-MM-DDTHH:MM:SS.NNNNNNN] |
2024-09-13T01:23:45.6789012 |
| DD Mon YYYY HH:MI:SS.NNNNNNNAM 1 |
Kuwaiti |
SELECT CONVERT(NVARCHAR(30), SYSDATETIME(), 130) |
13 Sep 2024 1:23:45.6789012AM1 |
| DD/MM/YYYY HH:MI:SS.NNNNNNNAM |
Kuwaiti |
SELECT CONVERT(NVARCHAR(30), SYSDATETIME(), 131) |
09/13/2024 1:23:45.6789012AM |
Figure 1: SYSDATETIME Date Formats Using CONVERT Without Century
Figure 2: SYSDATETIME Date Formats Using CONVERT With Century
Here are some more date formats that does not come standard in SQL Server as
part of the CONVERT function.
| Date Format |
SQL Statement |
Sample Output |
| YY-MM-DD |
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 20), 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 11), '/', '-') AS [YY-MM-DD]
SELECT FORMAT(SYSDATETIME(), 'yy-MM-dd') AS [YY-MM-DD]
|
24-09-13 |
| YYYY-MM-DD |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 111), '/', '-') AS [YYYY-MM-DD]
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd') AS [YYYY-MM-DD]
|
2024-09-13 |
| YYYY-M-D |
SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M-D]
SELECT FORMAT(SYSDATETIME(), 'yyyy-M-d') AS [YYYY-M-D]
|
2024-9-13 |
| YY-M-D |
SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M-D]
SELECT FORMAT(SYSDATETIME(), 'yy-M-d') AS [YY-M-D]
|
24-9-13 |
| M-D-YYYY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-D-YYYY]
SELECT FORMAT(SYSDATETIME(), 'M-d-yyyy') AS [M-D-YYYY]
|
9-13-2024 |
| M-D-YY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-D-YY]
SELECT FORMAT(SYSDATETIME(), 'M-d-yy') AS [M-D-YY]
|
9-13-24 |
| D-M-YYYY |
SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D-M-YYYY]
SELECT FORMAT(SYSDATETIME(), 'd-M-yyyy') AS [D-M-YYYY]
|
13-9-2024 |
| D-M-YY |
SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D-M-YY]
SELECT FORMAT(SYSDATETIME(), 'd-M-yy') AS [D-M-YY]
|
13-9-24 |
| YY-MM |
SELECT RIGHT(CONVERT(VARCHAR(7), SYSDATETIME(), 20), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), SYSDATETIME(), 120), 3, 5) AS [YY-MM]
SELECT FORMAT(SYSDATETIME(), 'yy-MM') AS [YY-MM] |
24-09 |
| YYYY-MM |
SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM]
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM') AS [YYYY-MM] |
2024-09 |
| YY-M |
SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M]
SELECT FORMAT(SYSDATETIME(), 'yy-MM') AS [YY-MM] |
24-09 |
| YYYY-M |
SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M]
SELECT FORMAT(SYSDATETIME(), 'yyyy-M') AS [YYYY-M] |
2024-9 |
| MM-YY |
SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 4, 5) AS [MM-YY]
SELECT FORMAT(SYSDATETIME(), 'MM-yy') AS [MM-YY] |
09-24 |
| MM-YYYY |
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY]
SELECT FORMAT(SYSDATETIME(), 'MM-yyyy') AS [MM-YYYY] |
09-2024 |
| M-YY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-YY]
SELECT FORMAT(SYSDATETIME(), 'M-yy') AS [M-YY] |
9-24 |
| M-YYYY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-YYYY]
SELECT FORMAT(SYSDATETIME(), 'M-yyyy') AS [M-YYYY] |
9-2024 |
| MM-DD |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD]
SELECT FORMAT(SYSDATETIME(), 'MM-dd') AS [MM-DD] |
09-13 |
| DD-MM |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM]
SELECT FORMAT(SYSDATETIME(), 'dd-MM') AS [DD-MM] |
13-09 |
| M-D |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M-D]
SELECT FORMAT(SYSDATETIME(), 'M-d') AS [M-D] |
9-13 |
| D-M |
SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D-M]
SELECT FORMAT(SYSDATETIME(), 'd-M') AS [D-M] |
13-9 |
| M/D/YYYY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/D/YYYY]
SELECT FORMAT(SYSDATETIME(), 'M/d/yyyy') AS [M/D/YYYY] |
9/13/2024 |
| M/D/YY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/D/YY]
SELECT FORMAT(SYSDATETIME(), 'M/d/yy') AS [M/D/YY] |
9/13/24 |
| D/M/YYYY |
SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D/M/YYYY]
SELECT FORMAT(SYSDATETIME(), 'd/M/yyyy') AS [D/M/YYYY] |
13/9/2024 |
| D/M/YY |
SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D/M/YY]
SELECT FORMAT(SYSDATETIME(), 'd/M/yy') AS [D/M/YY] |
13/9/24 |
| YYYY/M/D |
SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M/D]
SELECT FORMAT(SYSDATETIME(), 'yyyy/M/d') AS [YYYY/M/D] |
2024/9/13 |
| YY/M/D |
SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M/D]
SELECT FORMAT(SYSDATETIME(), 'yy/M/d') AS [YY/M/D] |
24/9/13 |
| MM/YY |
SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY]
SELECT FORMAT(SYSDATETIME(), 'MM/yy') AS [MM/YY] |
09/24 |
| MM/YYYY |
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY]
SELECT FORMAT(SYSDATETIME(), 'MM/yyyy') AS [MM/YYYY] |
09/2024 |
| M/YY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/YY]
SELECT FORMAT(SYSDATETIME(), 'M/yy') AS [M/YY] |
9/24 |
| M/YYYY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/YYYY]
SELECT FORMAT(SYSDATETIME(), 'M/yyyy') AS [M/YYYY] |
9/2024 |
| YY/MM |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM]
SELECT FORMAT(SYSDATETIME(), 'yy/MM') AS [YY/MM] |
24/09 |
| YYYY/MM |
SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM]
SELECT FORMAT(SYSDATETIME(), 'yyyy/MM') AS [YYYY/MM] |
2024/09 |
| YY/M |
SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M]
SELECT FORMAT(SYSDATETIME(), 'yy/M') AS [YY/M] |
24/9 |
| YYYY/M |
SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M]
SELECT FORMAT(SYSDATETIME(), 'yyyy/M') AS [YYYY/M] |
2024/9 |
| MM/DD |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD]
SELECT FORMAT(SYSDATETIME(), 'MM/dd') AS [MM/DD] |
09/13 |
| DD/MM |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM]
SELECT FORMAT(SYSDATETIME(), 'dd/MM') AS [DD/MM] |
13/09 |
| M/D |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M/D]
SELECT FORMAT(SYSDATETIME(), 'M/d') AS [M/D] |
9/13 |
| D/M |
SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D/M]
SELECT FORMAT(SYSDATETIME(), 'd/M') AS [D/M] |
13/9 |
| MM.DD.YYYY |
SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '.') AS [MM.DD.YYYY]
SELECT FORMAT(SYSDATETIME(), 'MM.dd.yyyy') AS [MM.DD.YYYY] |
09.13.2024 |
| MM.DD.YY |
SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '.') AS [MM.DD.YY]
SELECT FORMAT(SYSDATETIME(), 'MM.dd.yy') AS [MM.DD.YY] |
09.13.24 |
| M.D.YYYY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.D.YYYY]
SELECT FORMAT(SYSDATETIME(), 'M.d.yyyy') AS [M.D.YYYY] |
9.13.2024 |
| M.D.YY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.D.YY]
SELECT FORMAT(SYSDATETIME(), 'M.d.yy') AS [M.D.YY] |
9.13.24 |
| DD.MM.YYYY |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]
SELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY] |
13.09.2024 |
| DD.MM.YY |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY]
SELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY] |
13.09.24 |
| D.M.YYYY |
SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D.M.YYYY]
SELECT FORMAT(SYSDATETIME(), 'd.M.yyyy') AS [D.M.YYYY] |
13.9.2024 |
| D.M.YY |
SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D.M.YY]
SELECT FORMAT(SYSDATETIME(), 'd.M.yy') AS [D.M.YY] |
13.9.24 |
| YYYY.M.D |
SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M.D]
SELECT FORMAT(SYSDATETIME(), 'yyyy.M.d') AS [YYYY.M.D] |
2024.9.13 |
| YY.M.D |
SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M.D]
SELECT FORMAT(SYSDATETIME(), 'yy.M.d') AS [YY.M.D] |
24.9.13 |
| MM.YYYY |
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY]
SELECT FORMAT(SYSDATETIME(), 'MM.yyyy') AS [MM.YYYY] |
09.2024 |
| MM.YY |
SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY]
SELECT FORMAT(SYSDATETIME(), 'MM.yy') AS [MM.YY] |
09.24 |
| M.YYYY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.YYYY]
SELECT FORMAT(SYSDATETIME(), 'M.yyyy') AS [M.YYYY] |
9.2024 |
| M.YY |
SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.YY]
SELECT FORMAT(SYSDATETIME(), 'M.yy') AS [M.YY] |
9.24 |
| YYYY.MM |
SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM]
SELECT FORMAT(SYSDATETIME(), 'yyyy.MM') AS [YYYY.MM] |
2024.09 |
| YY.MM |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM]
SELECT FORMAT(SYSDATETIME(), 'yy.MM') AS [YY.MM] |
24.09 |
| YYYY.M |
SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M]
SELECT FORMAT(SYSDATETIME(), 'yyyy.M') AS [YYYY.M] |
2024.9 |
| YY.M |
SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M]
SELECT FORMAT(SYSDATETIME(), 'yy.M') AS [YY.M] |
24.9 |
| MM.DD |
SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD]
SELECT FORMAT(SYSDATETIME(), 'MM.dd') AS [MM.DD] |
09.13 |
| DD.MM |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 4) AS [DD.MM]
SELECT FORMAT(SYSDATETIME(), 'dd.MM') AS [DD.MM] |
13.09 |
| MMDDYYYY |
SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '') AS [MMDDYYYY]
SELECT FORMAT(SYSDATETIME(), 'MMddyyyy') AS [MMDDYYYY] |
09132024 |
| MMDDYY |
SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '') AS [MMDDYY]
SELECT FORMAT(SYSDATETIME(), 'MMddyy') AS [MMDDYY] |
091324 |
| DDMMYYYY |
SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', '') AS [DDMMYYYY]
SELECT FORMAT(SYSDATETIME(), 'ddMMyyyy') AS [DDMMYYYY] |
13092024 |
| DDMMYY |
SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY]
SELECT FORMAT(SYSDATETIME(), 'ddMMyy') AS [DDMMYY] |
130924 |
| MMYYYY |
SELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', ''), 6) AS [MMYYYY]
SELECT FORMAT(SYSDATETIME(), 'MMyyyy') AS [MMYYYY] |
092024 |
| MMYY |
SELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', ''), 4) AS [MMYY]
SELECT FORMAT(SYSDATETIME(), 'MMyy') AS [MMYY] |
0924 |
| YYYYMM |
SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM]
SELECT FORMAT(SYSDATETIME(), 'yyyyMM') AS [YYYYMM] |
202409 |
| YYMM |
SELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM]
SELECT FORMAT(SYSDATETIME(), 'yyMM') AS [YYMM] |
2409 |
| Month DD, YYYY 1 |
SELECT DATENAME(MONTH, SYSDATETIME())+ ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ', ' + DATENAME(YEAR, SYSDATETIME())
AS [Month DD, YYYY]
SELECT FORMAT(SYSDATETIME(), 'MMMM dd, yyyy') AS [Month DD, YYYY] |
September 13, 2024 1 |
| Mon YYYY 1 |
SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY]
SELECT FORMAT(SYSDATETIME(), 'MMM yyyy') AS [Mon YYYY] |
Sep 2024 1 |
| Month YYYY 1 |
SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS
[Month YYYY]
SELECT FORMAT(SYSDATETIME(), 'MMMM yyyy') AS [Month YYYY]
|
September 2024 1 |
| DD Month 1 |
SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME()) AS
[DD Month]
SELECT FORMAT(SYSDATETIME(), 'dd MMMM') AS [DD Month] |
13 September 1 |
| Month DD 1 |
SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) AS
[Month DD]
SELECT FORMAT(SYSDATETIME(), 'MMMM dd') AS [Month DD] |
September 13 1 |
| DD Month YY 1 |
SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ' ' + DATENAME(MM, SYSDATETIME()) + ' '
+ RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [DD Month YY]
SELECT FORMAT(SYSDATETIME(), 'dd MMMM yy') AS [DD Month YY] |
13 September 24 1 |
| DD Month YYYY 1 |
SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME())+ ' ' + DATENAME(YEAR, SYSDATETIME()) AS [DD Month YYYY]
SELECT FORMAT(SYSDATETIME(), 'dd MMMM yyyy') AS [DD Month YYYY] |
13 September 2024 1 |
| Mon-YY 1 |
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ' ', '-') AS
[Mon-YY]
SELECT FORMAT(SYSDATETIME(), 'MMM-yy') AS [Mon-YY] |
Sep-24 1 |
| Mon-YYYY 1 |
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS
[Mon-YYYY]
SELECT FORMAT(SYSDATETIME(), 'MMM-yyyy') AS [Mon-YYYY] |
Sep-2024 1 |
| DD-Mon-YY 1 |
SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ' ', '-') AS [DD-Mon-YY]
SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yy') AS [DD-Mon-YY] |
13-Sep-24 1 |
| DD-Mon-YYYY 1 |
SELECT REPLACE(CONVERT(VARCHAR(11), SYSDATETIME(), 106), ' ', '-') AS [DD-Mon-YYYY]
SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yyyy') AS [DD-Mon-YYYY] |
13-Sep-2024 1 |
1 To make the month name in upper case, simply use
the UPPER string function.
|