In this article, we will show you, How to perform DATE Format or formatting SQL Server Date and Time with an example.
Convert SQL DATE Format Example
Before we go toward the practical example, let me explain to you the available list of Convert date formats. We will write different SQL Server Date format Queries for this demonstration using CONVERT and FORMAT functions. The CONVERT function provides different styles to format date and time.
| Without Century (yy) | With Century (yyyy) | Input/Output | Standard |
|---|---|---|---|
| – | 0 to 100 | mon dd yyyy hh:miAM (or PM) | This is the Default for both datetime and smalldatetime |
| 1 | 101 | 1 = mm/dd/yy 101 = mm/dd/yyyy | U.S. |
| 2 | 102 | 2 = yy.mm.dd 102 = yyyy.mm.dd | ANSI |
| 3 | 103 | 3 = dd/mm/yy 103 = dd/mm/yyyy | British/French |
| 4 | 104 | 4 = dd.mm.yy 104 = dd.mm.yyyy | German |
| 5 | 105 | 5 = dd-mm-yy 105 = dd-mm-yyyy | Italian |
| 6 | 106 | 6 = dd mon yy 106 = dd mon yyyy | – |
| 7 | 107 | 7 = Mon dd, yy 107 = Mon dd, yyyy | – |
| 8 | 108 | hh:mi:ss | – |
| – | 9 or 109 | mon dd yyyy hh:mi:ss:mmmmAM (or PM) | Default + milliseconds |
| 10 | 110 | 10 = mm-dd-yy 110 = mm-dd-yyyy | USA |
| 11 | 111 | 11 = yy/mm/dd 111 = yyyy/mm/dd | JAPAN |
| 12 | 112 | 12 = yymmdd 112 = yyyymmdd | ISO |
| – | 13 or 113 | dd mon yyyy hh:mi:ss:mmm(24h) | Europe Default + millisecond |
| 14 | 114 | hh:mi:ss:mmm(24h) | – |
| – | 20 or 120 | yyyy-mm-dd hh:mi:ss(24h) | ODBC canonical |
| – | 21 or 121 | yyyy-mm-dd hh:mi:ss.mmm(24h) | ODBC is canonical with milliseconds. This is the Default for time, date, datetime2, and datetimeoffset |
| – | 126 | yyyy-mm-ddThh:mi:ss.mmm (no Spaces) | ISO8601 |
| – | 127 | yyyy-mm-ddThh:mi:ss.mmmZ (no Spaces) | ISO8601 with time zone Z |
| – | 130 | dd mon yyyy hh:mi:ss:mmmAM | Hijri |
| – | 131 | dd/mm/yyyy hh:mi:ss:mmmAM | Hijri |
SQL Server DATE Format using CONVERT Function
In this SQL example, we are going to use the CONVERT function on GETDATE() to return the date in different formats.
SELECT CONVERT(VARCHAR(20), GETDATE()) AS 'Result 1'; SELECT CONVERT(VARCHAR(20), GETDATE(), 0) AS 'Result 2'; SELECT CONVERT(VARCHAR(20), GETDATE(), 100) AS 'Result 3'; SELECT CONVERT(VARCHAR(20), GETDATE(), 1) AS 'Result 4'; SELECT CONVERT(VARCHAR(20), GETDATE(), 101) AS 'Result 5'; SELECT CONVERT(VARCHAR(20), GETDATE(), 2) AS 'Result 6'; SELECT CONVERT(VARCHAR(20), GETDATE(), 102) AS 'Result 7';
Convert DATE Format Example 2
This example query covers a few more of the Convert function
SELECT CONVERT(VARCHAR(20), GETDATE(), 3) AS 'Result 8'; SELECT CONVERT(VARCHAR(20), GETDATE(), 103) AS 'Result 9'; SELECT CONVERT(VARCHAR(20), GETDATE(), 4) AS 'Result 10'; SELECT CONVERT(VARCHAR(20), GETDATE(), 104) AS 'Result 11'; SELECT CONVERT(VARCHAR(20), GETDATE(), 5) AS 'Result 12'; SELECT CONVERT(VARCHAR(20), GETDATE(), 105) AS 'Result 13'; SELECT CONVERT(VARCHAR(20), GETDATE(), 6) AS 'Result 14'; SELECT CONVERT(VARCHAR(20), GETDATE(), 106) AS 'Result 15';
In this SQL Server example query, we are showing a few other date formats in the Convert function are
SELECT CONVERT(VARCHAR(20), GETDATE(), 7) AS 'Result 16'; SELECT CONVERT(VARCHAR(20), GETDATE(), 107) AS 'Result 17'; SELECT CONVERT(VARCHAR(20), GETDATE(), 8) AS 'Result 18'; SELECT CONVERT(VARCHAR(20), GETDATE(), 108) AS 'Result 19'; SELECT CONVERT(VARCHAR(20), GETDATE(), 9) AS 'Result 20'; SELECT CONVERT(VARCHAR(20), GETDATE(), 109) AS 'Result 21'; SELECT CONVERT(VARCHAR(20), GETDATE(), 10) AS 'Result 22'; SELECT CONVERT(VARCHAR(20), GETDATE(), 110) AS 'Result 23';
Some other convert date formats.
SELECT CONVERT(VARCHAR(20), GETDATE(), 11) AS 'Result 24'; SELECT CONVERT(VARCHAR(20), GETDATE(), 111) AS 'Result 25'; SELECT CONVERT(VARCHAR(20), GETDATE(), 12) AS 'Result 26'; SELECT CONVERT(VARCHAR(20), GETDATE(), 112) AS 'Result 27'; SELECT CONVERT(VARCHAR(20), GETDATE(), 113) AS 'Result 28'; SELECT CONVERT(VARCHAR(20), GETDATE(), 14) AS 'Result 29'; SELECT CONVERT(VARCHAR(20), GETDATE(), 114) AS 'Result 30'; SELECT CONVERT(VARCHAR(20), GETDATE(), 20) AS 'Result 31';
The following is the list of remaining options available in the SQL Server convert function for date format.
SELECT CONVERT(VARCHAR(35), GETDATE(), 21) AS 'Result 32'; SELECT CONVERT(VARCHAR(35), GETDATE(), 126) AS 'Result 33'; SELECT CONVERT(VARCHAR(35), GETDATE(), 127) AS 'Result 34'; SELECT CONVERT(NVARCHAR(35), GETDATE(), 130) AS 'Result 35'; SELECT CONVERT(NVARCHAR(35), GETDATE(), 131) AS 'Result 36';
SQL Date Format Function
In this example, we are going to use the FORMAT function on GETDATE() to return the date in different formats. I suggest you refer to the Standard Date and Time Strings to understand the string formats that we used in this example.
SELECT FORMAT(GETDATE(), 'd', 'en-US' ) AS 'Result 1' SELECT FORMAT(GETDATE(), 'D', 'en-US' ) AS 'Result 2' SELECT FORMAT(GETDATE(), 'f', 'en-US' ) AS 'Result 3' SELECT FORMAT(GETDATE(), 'F', 'en-US' ) AS 'Result 4' SELECT FORMAT(GETDATE(), 'g', 'en-US' ) AS 'Result 5' SELECT FORMAT(GETDATE(), 'G', 'en-US' ) AS 'Result 6' SELECT FORMAT(GETDATE(), 'm', 'en-US' ) AS 'Result 7' SELECT FORMAT(GETDATE(), 'M', 'en-US' ) AS 'Result 8'
The following is the list of remaining variations that are available in the format function for date format.
SELECT FORMAT(GETDATE(), 'O', 'en-US' ) AS 'Result 9' SELECT FORMAT(GETDATE(), 'R', 'en-US' ) AS 'Result 10' SELECT FORMAT(GETDATE(), 's', 'en-US' ) AS 'Result 11' SELECT FORMAT(GETDATE(), 'S', 'en-US' ) AS 'Result 12' SELECT FORMAT(GETDATE(), 't', 'en-US' ) AS 'Result 13' SELECT FORMAT(GETDATE(), 'T', 'en-US' ) AS 'Result 14' SELECT FORMAT(GETDATE(), 'u', 'en-US' ) AS 'Result 15' SELECT FORMAT(GETDATE(), 'U', 'en-US' ) AS 'Result 16' SELECT FORMAT(GETDATE(), 'Y', 'en-US' ) AS 'Result 17'
format using Datetime Functions
Here, we will use the DATETIME functions that are available to format date and time in SQL Server to return the date in different formats.
SELECT DATEFROMPARTS(2017, 06, 14) AS 'Result 1';
SELECT DATETIMEFROMPARTS(2017, 06, 14, 11, 57, 53, 847) AS 'Result 3';
SELECT EOMONTH('20170614') AS 'Result 3';
format using Conversion Functions
In this example, we are going to use the SQL Server Conversion Functions to format the Date and Time. And the Conversation functions are PARSE, TRY_PARSE, CONVERT, and TRY_CONVERT. We use these functions on different dates to return the same in different variations.
SELECT PARSE('Wednesday, June 14, 2017' AS DATETIME USING 'en-US') AS 'Result4';
SELECT TRY_PARSE('Wednesday, June 14, 2017' AS DATETIME USING 'en-US') AS 'Result5';
SELECT CONVERT(DATETIME2, SYSDATETIME()) AS 'Result6';
SELECT TRY_CONVERT(DATETIME2, '6/14/2017 11:57:53 AM') AS 'Result7';
