SQL Date Format Examples using CONVERT Function

Problem

When querying SQL Server data, dates and times may not always be formatted the way you want. In this article, we look at how to use the SQL CONVERT function to format dates and times in over 100 different formats.

Solution

SQL Server provides a number of date and time formatting options and in this article we look at how to use SQL CONVERT to output different date/time formats such as mm/dd/yy, mm/dd/yyyy, dd-mm-yyyy, etc.

Examples to get SQL Date Format in SQL Server

• Use SELECT CONVERT and the format option for the date/time format needed
• To get YYYY-MM-DD use:
  • SELECT CONVERT(varchar, getdate(), 23)
• To get MM/DD/YY use:
  • SELECT CONVERT(varchar, getdate(), 1)
• See the table below for all options

SQL Convert Date and Time Format Options

Check out the table below for all of the different format options for Dates and Times.

The date used for all of these examples is “2022-12-30 00:38:54.840” (December 30, 2022). The format is yyyy-mm-dd hh:mm:ss:nnn.

• yyyy – year
• mm – month
• dd – day
• hh – hour
• mm – minute
• ss – second
• nnn – milisecond

The below queries use the GETDATE() function if you want to return the current date and time.

DATE ONLY FORMATS
Format #	Query	Format	Sample
1	select convert(varchar, getdate(), 1)	mm/dd/yy	12/30/22
2	select convert(varchar, getdate(), 2)	yy.mm.dd	22.12.30
3	select convert(varchar, getdate(), 3)	dd/mm/yy	30/12/22
4	select convert(varchar, getdate(), 4)	dd.mm.yy	30.12.22
5	select convert(varchar, getdate(), 5)	dd-mm-yy	30-12-22
6	select convert(varchar, getdate(), 6)	dd Mon yy	30 Dec 22
7	select convert(varchar, getdate(), 7)	Mon dd, yy	Dec 30, 22
10	select convert(varchar, getdate(), 10)	mm-dd-yy	12-30-22
11	select convert(varchar, getdate(), 11)	yy/mm/dd	22/12/30
12	select convert(varchar, getdate(), 12)	yymmdd	221230
23	select convert(varchar, getdate(), 23)	yyyy-mm-dd	2022-12-30
31	select convert(varchar, getdate(), 31)	yyyy-dd-mm	2022-30-12
32	select convert(varchar, getdate(), 32)	mm-dd-yyyy	12-30-2022
33	select convert(varchar, getdate(), 33)	mm-yyyy-dd	12-2022-30
34	select convert(varchar, getdate(), 34)	dd-mm-yyyy	30-12-2022
35	select convert(varchar, getdate(), 35)	dd-yyyy-mm	30-2022-12
101	select convert(varchar, getdate(), 101)	mm/dd/yyyy	12/30/2022
102	select convert(varchar, getdate(), 102)	yyyy.mm.dd	2022.12.30
103	select convert(varchar, getdate(), 103)	dd/mm/yyyy	30/12/2022
104	select convert(varchar, getdate(), 104)	dd.mm.yyyy	30.12.2022
105	select convert(varchar, getdate(), 105)	dd-mm-yyyy	30-12-2022
106	select convert(varchar, getdate(), 106)	dd Mon yyyy	30 Dec 2022
107	select convert(varchar, getdate(), 107)	Mon dd, yyyy	Dec 30, 2022
110	select convert(varchar, getdate(), 110)	mm-dd-yyyy	12-30-2022
111	select convert(varchar, getdate(), 111)	yyyy/mm/dd	2022/12/30
112	select convert(varchar, getdate(), 112)	yyyymmdd	20221230
TIME ONLY FORMATS
8	select convert(varchar, getdate(), 8)	hh:mm:ss	00:38:54
14	select convert(varchar, getdate(), 14)	hh:mm:ss:nnn	00:38:54:840
24	select convert(varchar, getdate(), 24)	hh:mm:ss	00:38:54
108	select convert(varchar, getdate(), 108)	hh:mm:ss	00:38:54
114	select convert(varchar, getdate(), 114)	hh:mm:ss:nnn	00:38:54:840
DATE & TIME FORMATS
0	select convert(varchar, getdate(), 0)	Mon dd yyyy hh:mm AM/PM	Dec 30 2022 12:38AM
9	select convert(varchar, getdate(), 9)	Mon dd yyyy hh:mm:ss:nnn AM/PM	Dec 30 2022 12:38:54:840AM
13	select convert(varchar, getdate(), 13)	dd Mon yyyy hh:mm:ss:nnn AM/PM	30 Dec 2022 00:38:54:840AM
20	select convert(varchar, getdate(), 20)	yyyy-mm-dd hh:mm:ss	2022-12-30 00:38:54
21	select convert(varchar, getdate(), 21)	yyyy-mm-dd hh:mm:ss:nnn	2022-12-30 00:38:54.840
22	select convert(varchar, getdate(), 22)	mm/dd/yy hh:mm:ss AM/PM	12/30/22 12:38:54 AM
25	select convert(varchar, getdate(), 25)	yyyy-mm-dd hh:mm:ss:nnn	2022-12-30 00:38:54.840
26	select convert(varchar, getdate(), 26)	yyyy-dd-mm hh:mm:ss:nnn	2022-30-12 00:38:54.840
27	select convert(varchar, getdate(), 27)	mm-dd-yyyy hh:mm:ss:nnn	12-30-2022 00:38:54.840
28	select convert(varchar, getdate(), 28)	mm-yyyy-dd hh:mm:ss:nnn	12-2022-30 00:38:54.840
29	select convert(varchar, getdate(), 29)	dd-mm-yyyy hh:mm:ss:nnn	30-12-2022 00:38:54.840
30	select convert(varchar, getdate(), 30)	dd-yyyy-mm hh:mm:ss:nnn	30-2022-12 00:38:54.840
100	select convert(varchar, getdate(), 100)	Mon dd yyyy hh:mm AM/PM	Dec 30 2022 12:38AM
109	select convert(varchar, getdate(), 109)	Mon dd yyyy hh:mm:ss:nnn AM/PM	Dec 30 2022 12:38:54:840AM
113	select convert(varchar, getdate(), 113)	dd Mon yyyy hh:mm:ss:nnn	30 Dec 2022 00:38:54:840
120	select convert(varchar, getdate(), 120)	yyyy-mm-dd hh:mm:ss	2022-12-30 00:38:54
121	select convert(varchar, getdate(), 121)	yyyy-mm-dd hh:mm:ss:nnn	2022-12-30 00:38:54.840
126	select convert(varchar, getdate(), 126)	yyyy-mm-dd T hh:mm:ss:nnn	2022-12-30T00:38:54.840
127	select convert(varchar, getdate(), 127)	yyyy-mm-dd T hh:mm:ss:nnn	2022-12-30T00:38:54.840
ISLAMIC CALENDAR DATES
130	select convert(nvarchar, getdate(), 130)	dd mmm yyyy hh:mi:ss:nnn AM/PM
131	select convert(nvarchar, getdate(), 131)	dd mmm yyyy hh:mi:ss:nnn AM/PM	7/06/1444 12:38:54:840AM

Format Date or Time without Dividing Characters

Format the date or time without dividing characters and concatenate the date and time string:

Another option is to use the SQL FORMAT function as follows:

SELECT FORMAT(GETDATE(), 'yyyyMMddHHmmss')

Get List of all Valid Date and Time Formats

Get a list of all valid date and time formats, using the code below. Change the @date to GETDATE() or any other date you want to use.  This will output just the valid formats.

DECLARE @counter INT = 0
DECLARE @date DATETIME = '2022-12-30 00:38:54.840'
CREATE TABLE #dateFormats (dateFormatOption int, dateOutput nvarchar(40))
WHILE (@counter <= 150 )
BEGIN
   BEGIN TRY
      INSERT INTO #dateFormats
      SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter >= 150
      BEGIN
         BREAK
      END
   END CATCH
END
SELECT * FROM #dateFormats

Table of Date Data Types in SQL Server

Here are links to date data type articles:

• Working with Date and Time Data Types in SQL Server
• Understanding Time Zones in SQL Server
• Convert SQL Server DateTime Data Type to DateTimeOffset Data Type
• SQL Server Date Time Calculation Examples
• SQL Server Date and Time Functions with Examples

SQL Date Format dd/mm/yyyy with SQL CONVERT

First is an example for the SQL date format as dd/mm/yyyy. This is two-digit day, two-digit month and four-digit year output.

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--dd/mm/yyyy with 4 DIGIT YEAR
SELECT CONVERT(VARCHAR(10), @Datetime, 103) CurrentDateFormattedAsText;
 
--dd/mm/yy with 2 DIGIT YEAR
SELECT CONVERT(VARCHAR(8), @Datetime,   3) CurrentDateFormattedAsText;

-- pull data from a database table
-- The date used for this example was January 25, 2013. 
--SELECT a datetime column as a string formatted dd/mm/yyyy (4 digit year)
SELECT TOP 3 CONVERT(CHAR(10), ExpectedDeliveryDate, 103) ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted dd/mm/yy (2 digit year)
SELECT TOP 3 CONVERT(CHAR(8), ExpectedDeliveryDate, 3) ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

Once the query runs, review SQL CONVERT output as shown below.

SQL Date Format mm/dd/yyyy with SQL CONVERT

Second, is SQL CONVERT example for the two-digit month, two-digit day and four-digit year.

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--mm/dd/yyyy with 4 DIGIT YEAR
SELECT CONVERT(VARCHAR(10), @Datetime, 101) CurrentDateFormattedAsText;
 
--mm/dd/yy with 2 DIGIT YEAR
SELECT CONVERT(VARCHAR(8), @Datetime,   1) CurrentDateFormattedAsText;

-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted mm/dd/yyyy (4 digit year)
SELECT TOP 3 CONVERT(CHAR(10), ExpectedDeliveryDate, 101) ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted mm/dd/yy (2 digit year)
SELECT TOP 3 CONVERT(CHAR(8), ExpectedDeliveryDate, 1) ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

Next, review the output from the T-SQL code above.

SQL Date Format yyyy mm dd with SQL CONVERT

Third is an example of SQL CONVERT formatting the date as a four-digit year, two-digit month and two-digit day without any slashes.

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--yyyy mm dd with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 102), '.', ' ') CurrentDateFormattedAsText;
 
--yy mm dd  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,   2), '.', ' ') CurrentDateFormattedAsText;

-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted yyyy mm dd (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 102), '.', ' ') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted yy mm dd (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 2), '.', ' ') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

Note the SQL CONVERT logic output below without any slashes.

SQL Date Format yyyymmdd with SQL CONVERT

Fourth, is SQL CONVERT example without slashes or spaces separating the year, month and day.

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--yyyymmdd with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 112), '.', ' ') CurrentDateFormattedAsText;
 
--yymmdd  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,  12), '.', ' ') CurrentDateFormattedAsText;

-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted yyyymmdd (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 112), '.', ' ') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted yymmdd (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 12), '.', ' ') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

The output from the logic is shown below:

SQL Date format ddmmyyyy with SQL CONVERT

Fifth, is a code example that changes the order of the date and excludes slashes or spaces in the date format.

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--ddmmyyyy with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 104), '.', '') CurrentDateFormattedAsText;
 
--ddmmyy  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,   4), '.', '') CurrentDateFormattedAsText;

-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted ddmmyyyy (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 104), '.', '') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted ddmmyy (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate,  4), '.', '') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

The screen shot below includes multiple sets of output for dates without slashes or spaces.

SQL Date Format yyyy-mm-dd with SQL CONVERT

SQL CONVERT code for a four-digit year, two-digit month and two-digit date.

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--yyyy-mm-dd with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 111), '/', '-') CurrentDateFormattedAsText;
 
--yy-mm-dd  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,   11), '/', '-') CurrentDateFormattedAsText;

-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted yyyy-mm-dd (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 111), '/', '-') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted yy-mm-dd (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 11), '/', '-') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

Consider this output to use as a prefix for backup files to easily sort the file name by date.

SQL Date Format mm-dd-yyyy with SQL CONVERT

Seventh is SQL CONVERT example where the slashes are replaced by dashes. This can be accomplished using CONVERT option 110.

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--mm/dd/yyyy with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 110), '/', '-') CurrentDateFormattedAsText;
 
--mm/dd/yy  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,   10), '/', '-') CurrentDateFormattedAsText;

-- pull data from a database table
-- The date used for this example was January 15, 2013. 
--SELECT a datetime column as a string formatted mm/dd/yyyy (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 110), '/', '-') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted mm/dd/yy (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 10), '/', '-') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

Here is the output.

Recommended Reading

Continue your learning on Microsoft SQL Server dates with these tips and tutorials which could be valuable to beginners and experienced Developers alike:

• Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions
• SQL Server Date and Time Data Types
• SQL Server function to convert integer date to datetime format
• SQL Database DateTime Best Practices
• Format SQL Server Dates with FORMAT Function
• SQL Server Date Functions
• Add and Subtract Dates using DATEADD in SQL Server
• DATEDIFF SQL Server Function
• SQL Date Function Tutorial – DATEADD, DATEDIFF, DATENAME, DATEPART and more
• Getting Started with SQL DATEDIFF and DATEDIFF_BIG Functions with Use Cases
• How SQL Server handles the date format YYYY-MM-DD
• SQL Convert Date to YYYYMMDD
• CAST and CONVERT (Transact-SQL)
• SQL Server CONVERT() Function

Next Steps

• The formats listed above are not inclusive of all formats provided. Experiment with various dates and the different format numbers to see what others are available.
• Date formats can be used for all date/time functions and data served to client applications. Experiment with these data format conversions.
• Check out the SQL Server FORMAT Function to Format Dates.

Last updated by Sebastiao Pereira and Greg Robidoux on 2025-09-26.

MSSQL Tips

For 20+ years, MSSQLTips.com has been delivering comprehensive technical solutions on a daily basis for the Microsoft SQL Server platform.  In that time, an innovative team of over 300 authors from around the globe have delivered over 7000 articles and 1000 webcasts ranging from beginner topics to advanced solutions.  Our community prides itself on delivering human only generated content.