SQL DATE Format

In this article, we will show you, How to perform SQL DATE Format, or formatting SQL Server Date and Time with an example.

This is one of the SQL Server Frequently Asked Question, and for this demonstration, we are going to write different SQL Queries using CONVERT, and FORMAT function.

SQL DATE Format using CONVERT Function

Before we get into the practical example, let me show you the list of date format in SQL CONVERT function (formatting styles).

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 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 DATE Format CONVERT Example

In this example we are going to use the CONVERT function on GETDATE() to return the date in different formats.

--Using CONVERT Function for SQL Server Date Format
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';

OUTPUT

SQL DATE Format CONVERT Example 2

and the other date formats in SQL Convert function are

--Using CONVERT Function for SQL Server Date Format
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';

OUTPUT

SQL DATE Format CONVERT Example 3

Few other date formats in SQL Convert function are

--Using CONVERT Function for SQL Server Date Format
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';

OUTPUT

Some other convert date format in SQL

--Using CONVERT Function for SQL Server Date Format
--Using CONVERT Function for SQL Server Date Format
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';

OUTPUT

SQL DATE Format CONVERT Example 3

The following are the list of remaining formatting options available in convert function for date format in SQL Server

--Using CONVERT Function for SQL Server 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';

OUTPUT

SQL Date Format using 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 to refer the Standard SQL Date and Time Format Strings to understand the string formats that we used in this example.

--Using FORMAT Function for SQL Server Date Format
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'

OUTPUT

Use FORMAT Function to format Date in SQL Server

The following are the list of remaining formats that are available in format function to format date in SQL Server.

-- Using FORMAT Function for SQL Server 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'

OUTPUT

SQL DATE Format using DATE and TIME Functions

Here, we are going to use the DATE and TIME functions to format date in SQL Server to return the date in different formats.

--Using Different Date and Time functions for SQL Server Date Format
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';

OUTPUT

Conversion Functions to Format Date in SQL

In this example we are going to use the Conversion Functions to format date in SQL Server. And the Conversation functions are PARSE, TRY_PARSE, CONVERT, and TRY_CONVERT. We use these functions are different dates to return the date in different formats.

--Using Different Conversion functions for SQL Server Date Format
SELECT PARSE('Wednesday, June 14, 2017' AS DATETIME USING 'en-US') AS 'Result 4'; 

SELECT TRY_PARSE('Wednesday, June 14, 2017' AS DATETIME USING 'en-US') AS 'Result 5'; 

SELECT CONVERT(DATETIME2, SYSDATETIME()) AS 'Result 6'; 

SELECT TRY_CONVERT(DATETIME2, '6/14/2017 11:57:53 AM') AS 'Result 7';

OUTPUT

Users who want to save the SQL Server date Formats provided by CONVERT function in your mobiles can use the following image:

Thank You for Visiting Our Blog