SQL Server 2012 FORMAT Function vs. CONVERT Function
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 and the corresponding syntax using the new SQL Server 2012 FORMAT string function.
It is worth to note that the outputs of these date formats are of VARCHAR data types already and not of DATETIME data type.
FORMAT Function VS. CONVERT Function
|
|||
Date Format
|
FORMAT Function
|
CONVERT Function
|
Sample
Output
|
Mon
DD YYYY 1
HH:MIAM (or PM) |
SELECT
FORMAT(SYSDATETIME(), 'Mon d yyyy h:mmtt')
|
SELECT
CONVERT(VARCHAR(20), SYSDATETIME(), 100)
|
Jun
8 2011 1:30PM 1
|
MM/DD/YY
|
SELECT
FORMAT(SYSDATETIME(), 'MM/dd/yy') AS [MM/DD/YY]
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]
|
06/08/11
|
MM/DD/YYYY
|
SELECT
FORMAT(SYSDATETIME(), 'MM/dd/yyyy') AS [MM/DD/YYYY]
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]
|
06/08/2011
|
YY.MM.DD
|
SELECT
FORMAT(SYSDATETIME(), 'yy.MM.dd') AS [YY.MM.DD]
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]
|
11.06.08
|
YYYY.MM.DD
|
SELECT
FORMAT(SYSDATETIME(), 'yyyy.MM.dd') AS [YYYY.MM.DD]
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]
|
2011.06.08
|
DD/MM/YY
|
SELECT
FORMAT(SYSDATETIME(), 'dd/MM/yy') AS [DD/MM/YY]
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]
|
08/06/11
|
DD/MM/YYYY
|
SELECT
FORMAT(SYSDATETIME(), 'dd/MM/yyyy') AS [DD/MM/YYYY]
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]
|
08/06/2011
|
DD.MM.YY
|
SELECT
FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY]
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]
|
08.06.11
|
DD.MM.YYYY
|
SELECT
FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY]
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]
|
08.06.2011
|
DD-MM-YY
|
SELECT
FORMAT(SYSDATETIME(), 'dd-MM-yy') AS [DD-MM-YY]
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]
|
08-06-11
|
DD-MM-YYYY
|
SELECT
FORMAT(SYSDATETIME(), 'dd-MM-yyyy') AS [DD-MM-YYYY]
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]
|
08-06-2011
|
DD
Mon YY 1
|
SELECT
FORMAT(SYSDATETIME(), 'dd MMM yy') AS [DD MON YY]
|
SELECT
CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]
|
08
Jun 11 1
|
DD
Mon YYYY 1
|
SELECT
FORMAT(SYSDATETIME(), 'dd MMM yyyy') AS [DD MON YYYY]
|
SELECT
CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]
|
08
Jun 2011 1
|
Mon
DD, YY 1
|
SELECT
FORMAT(SYSDATETIME(), 'MMM dd, yy') AS [Mon DD, YY]
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]
|
Jun
08, 11 1
|
Mon
DD, YYYY 1
|
SELECT
FORMAT(SYSDATETIME(), 'MMM dd, yyyy') AS [Mon DD, YYYY]
|
SELECT
CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]
|
Jun
08, 2011 1
|
HH:MM:SS
|
SELECT
FORMAT(SYSDATETIME(), 'HH:mm:ss')
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 8)
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108) |
13:30:45
|
Mon
D YYYY H:MI:SS.NNNNNNNAM (or PM) 1
|
SELECT
FORMAT(SYSDATETIME(), 'MMM d yyyy h:mm:ss.ffffffftt')
|
SELECT
CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109) |
Jun
8 2011 1:30:45.9428675PM 1
|
MM-DD-YY
|
SELECT
FORMAT(SYSDATETIME(), 'MM-dd-yy') AS [MM-DD-YY]
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]
|
06-08-11
|
MM-DD-YYYY
|
SELECT
FORMAT(SYSDATETIME(), 'MM-dd-yyyy') AS [MM-DD-YYYY]
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]
|
06-08-2011
|
YY/MM/DD
|
SELECT
FORMAT(SYSDATETIME(), 'yy/MM/dd') AS [YY/MM/DD]
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]
|
11/06/08
|
YYYY/MM/DD
|
SELECT
FORMAT(SYSDATETIME(), 'yyyy/MM/dd') AS [YYYY/MM/DD]
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]
|
2011/06/08
|
YYMMDD
|
SELECT
FORMAT(SYSDATETIME(), 'yyMMdd') AS [YYMMDD]
|
SELECT
CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]
|
110608
|
YYYYMMDD
|
SELECT
FORMAT(SYSDATETIME(), 'yyyyMMdd') AS [YYYYMMDD]
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]
|
20110608
|
DD
Mon YYYY HH:MM:SS.NNNNNNN(24h) 1
|
SELECT
FORMAT(SYSDATETIME(), 'dd MMM yyyyHH:mm:ss.fffffff')
|
SELECT
CONVERT(VARCHAR(30), SYSDATETIME(), 13)
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113) |
08
Jun 2011 13:30:45.94286751
|
HH:MI:SS.NNNNNNN(24H)
|
SELECT
FORMAT(SYSDATETIME(), 'HH:mm:ss.fffffff') AS [HH:MI:SS:MMM(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)] |
13:30:45.9428675
|
YYYY-MM-DD
HH:MI:SS(24h)
|
SELECT
FORMAT(SYSDATETIME(), 'yyyy-MM-ddHH:mm:ss')
|
SELECT
CONVERT(VARCHAR(19), SYSDATETIME(), 120)
|
2011-06-08
13:30:45
|
YYYY-MM-DD
HH:MI:SS.NNNNNNN(24h)
|
SELECT
FORMAT(SYSDATETIME(), 'yyyy-MM-ddHH:mm:ss.fffffff')
|
SELECT
CONVERT(VARCHAR(23), SYSDATETIME(), 121)
|
2011-06-08
13:30:45.9428675
|
MM/DD/YY
HH:MI:SS AM
|
SELECT
FORMAT(SYSDATETIME(), 'MM/dd/yy h:mm:ss tt')
|
SELECT
CONVERT(VARCHAR(20), SYSDATETIME(), 22)
|
06/08/11
1:30:45 PM
|
YYYY-MM-DD
|
SELECT
FORMAT(SYSDATETIME(), 'yyyy-MM-dd')
|
SELECT
CONVERT(VARCHAR(26), SYSDATETIME(), 23)
|
2011-06-091
|
HH:MI:SS
(24h)
|
SELECT
FORMAT(SYSDATETIME(), 'HH:mm:ss')
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 24)
|
13:30:45
|
YYYY-MM-DD
HH:MI:SS.NNNNNNN
|
SELECT
FORMAT(SYSDATETIME(), 'yyyy-MM-ddHH:mm:ss.fffffff')
|
SELECT
CONVERT(VARCHAR(26), SYSDATETIME(), 25)
|
2011-06-08
13:30:45.94286751
|
YYYY-MM-DDTHH:MM:SS:NNNNNNN
|
SELECT
FORMAT(SYSDATETIME(), 'yyyy-MM-ddTHH:mm:ss.fffffff')
|
SELECT
CONVERT(VARCHAR(27), SYSDATETIME(), 126)
|
2011-06-08T13:30:45.9428675
|
DD
Mon YYYY HH:MI:SS.NNNNNNNAM 1
|
SELECT
FORMAT(SYSDATETIME(), 'dd MMM yyyy h:mm:ss.ffffffftt')
|
SELECT
CONVERT(VARCHAR(26), SYSDATETIME(), 130)
|
08
Jun 2011 1:30:45.9428675PM1
|
DD/MM/YYYY
HH:MI:SS.NNNNNNNAM
|
SELECT
FORMAT(SYSDATETIME(), 'dd/MM/yyyy h:mm:ss.ffffffftt')
|
SELECT
CONVERT(VARCHAR(25), SYSDATETIME(), 131)
|
08/06/2011
1:30:45.9428675PM
|
Post a Comment