Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : How to Get a List of Databases Within a SQL Server Instance
SQL Server 2008

Home > SQL Server 2008 > SQL Server 2008 Date Formats
SQL Server 2008 Date Format

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.

Standard Date Formats
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.

Extended Date Formats
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.

Related Articles :