SQL Convert Date to YYYYMMDD

By:   |   Updated: 2023-10-31   |   Comments (7)   |   Related: 1 | 2 | 3 | > Dates


Often when working with dates in SQL Server you may want to use the Year, Month, Day format 'yyyymmdd' as output or to filter your SQL query results. This is a condensed way to display the Date in a sortable format. This format can be used when you do not want to show the delimiter between the year, month, and day. This is a good option when looking for a consistent standard for an international audience and you do not need to include the time portion of a date stamp. However, when using this format as a filter there may be a gotcha for beginners that you should be aware of.


In this tutorial I'll set up a test table of date values with various data types: DATE, DATETIME, CHAR(8) and load sample date and show examples of outputs and filtering. I will use the CAST and CONVERT functions for date conversions in Microsoft SQL Server.

Create a Test Table

For this step we will create a test table: dbo.TestDate and load it with sample data. Here is the T-SQL syntax:

Use Tempdb;

CREATE TABLE [dbo].[TestDate] (
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [MyDate] [date]  NULL, -- date column
   [CharDate] [char](8) NULL, -- date column
   [MyDateTime] [datetime] NULL -- date column

Load Sample Test Data

For this limited example we will only load 20 rows of test data. For the data load to convert the date to 'yyyymmdd' format, I will use CONVERT(CHAR(8), TheDate, 112). Format 112 is the ISO standard for yyyymmdd.


-- Declare Parameters
DECLARE @SetDateTime DATETIME = '2020-01-01 14:04:03.230'; -- current date or GETDATE()

INSERT INTO [dbo].[TestDate]
SELECT CAST(Convert(CHAR(8),@SetDateTime,112) as DATETIME), Convert(CHAR(8),@SetDateTime,112), CAST(Convert(CHAR(8),@SetDateTime,112) as DATETIME);

INSERT INTO [dbo].[TestDate]
SELECT @SetDateTime, Convert(CHAR(8),@SetDateTime,112), @SetDateTime

INSERT INTO [dbo].[TestDate] ([MyDate] ,[CharDate], [MyDateTime])
SELECT top 1 [MyDateTime]+1, Convert(CHAR(8),[MyDateTime]+1,112), [MyDateTime]+1
FROM [dbo].[TestDate]
ORDER BY 1 desc 
GO 20  --load 20 days of dates

Results: Example SQL Server dates loaded.

Results of Sample DateTime Table.

Convert Dates to Char 'yyyymmdd'

Next, converting a DATE and DATETIME datatype to character 8 'yyyymmdd' output using CONVERT and FORMAT functions.

--A. CONVERT use style = 112 to CHAR 8 or NCHAR 8
SELECT CONVERT(CHAR(8),[MyDate],112) as 'MyDate',CONVERT(CHAR(8),[MyDateTime],112) as 'MyDateTime'
FROM [dbo].[TestDate];
--B. NCHAR(8)
SELECT CONVERT(NCHAR(8),[MyDate],112) as 'MyDate',CONVERT(NCHAR(8),[MyDateTime],112) as 'MyDateTime'
FROM [dbo].[TestDate];
--C. FORMAT Function (new in SQL 2012) use format = yyyyMMdd returning the results as nvarchar.
SELECT FORMAT([MyDate],'yyyyMMdd') as 'MyDate', FORMAT([MyDateTime],'yyyyMMdd')  as 'MyDateTime'
FROM [dbo].[TestDate]; 

Results: The results of the 3 queries all show the conversion to 'yyyymmdd' regardless of the format or the conversion method. The results below were reduced to 3 rows each for the demo.

Convert Dates to Char.

Convert Char 'yyyymmdd' back to Date data types in SQL Server

Now, convert the Character format 'yyyymmdd' to a Date and DateTime data type using CAST and CONVERT.

--A. Cast and Convert datatype DATE:
SELECT [CharDate],
      CAST([CharDate] AS DATE) as 'Date-CAST',
      CONVERT(DATE,[CharDate]) as 'Date-CONVERT'
FROM [dbo].[TestDate];
--B. Cast and Convert datatype DATETIME:
SELECT [CharDate],
      CAST([CharDate] AS DATETIME) as 'DateTime-CAST',
      CONVERT(DATETIME,[CharDate]) as 'DateTime-CONVERT'
FROM [dbo].[TestDate];

Results: Below shows the results of converting CHAR 'yyyymmdd' to a DATE and DATETIME data types! SQL does this gracefully.

Convert Char to Dates.

Filtering Dates by Char 'yyyymmdd' and the Gotcha

Using Character 'yyyymmdd' as a filter in the WHERE clause against date datatypes.

-- Test 'YYYYMMDD' filter against Date datatypes

--A. DATE datatype
FROM [dbo].[TestDate]
WHERE [MyDate] = '20200101'

--B.  DATETIME datatype
SELECT [MyDateTime]
FROM [dbo].[TestDate]
WHERE [MyDateTime] = '20200101' --implicit conversion to datetime Midnight!;

Results: Note the difference in the 2 result sets. When filtering against a DATETIME datatype, SQL implicitly converts the Character and appends a Midnight timestamp. Any rows with MyDateTime value other than midnight are excluded, i.e. 2020-01-01 14:04:03.230. This is the 'Gotcha'!

Filter and Gotcha

How to use Char 'yyyymmdd' filter against Datetime datatype to adjust for the Gotcha

When using the 'yyyymmdd' in the filter against DateTime datatype you must account for the timestamp part of the datatype!

--A. Use Greater Than and Equal and Less Than to get all dates with a Char 'yyyymmdd' Filter:
FROM [dbo].[TestDate]
WHERE [MyDateTime] >= '20200101' 
  AND [MyDateTime] < '20200102' --implicit conversion to datetime Midnight!;

What happens if we filter with yyyyymmdd as an Integer value?

--A. Test yyyymmdd filter against Date datatype using and integer 
FROM [dbo].[TestDate]
WHERE [MyDate] = 20200101;

Results: We can't do this because it results in an error!

Error Int Filter

Alternative Formats

Last, I will share examples of alternative formats for returning dates. Also, I will show how robust a SQL SELECT statement is at converting the other formats when used in a where clause.

--A. Alternative formats that returns Year Month Day mixing Date and Datetime formats using CONVERT:
SELECT CONVERT(CHAR(10),[MyDate],120) as 'MyDate_w_Dash',
       CONVERT(CHAR(10),[MyDateTime],111) as 'MyDateTime_w_Slash',
       CONVERT(CHAR(10),[MyDateTime],102) as 'MyDateTime_w_Dot'
FROM [dbo].[TestDate];

--B. Alternative formats that returns Year Month Day mixing Date and Datetime formats using FORMAT:
SELECT FORMAT([MyDate],'yyyy-MM-dd') as 'MyDate_w_Dash',
       FORMAT([MyDate],'yyyy/MM/dd') as 'MyDate_w_Slash',
       FORMAT([MyDateTime],'yyyy.MM.dd') as 'MyDateTime_w_Dot'
FROM [dbo].[TestDate];
--C. Note using Year, month, day in the where clause that SQL server will recognize different delimiters: dash, slash, dot or no delimiter as shown above.
FROM [dbo].[TestDate]
WHERE [MyDateTime] = '2020.01.01' --or '2020/01/01' or '2020-01-01' or '20200101'

Results: Review the 3 result sets from the queries! Note the different date delimiters and the ability to use different delimited dates as filters.

Alternate Formats.

Wrap Up

I hope you enjoyed this exercise on year, month, day date formats and can see the flexibility that SQL Server has handling dates. Other ways to take advantage of the Year Month Day short format, might include concatenating a date to a file name or naming a monthly billing batch. I have also seen the use of Char(6) YYYYMM to denote financial monthly periods. Please comment on other ways you may have used the yyyymmdd date format!

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-10-31

Comments For This Article

Tuesday, January 24, 2023 - 5:18:58 AM - Keyvan Back To Top (90847)
Very good.

Tuesday, August 3, 2021 - 7:23:33 AM - Calin Back To Top (89088)
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15: Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. In other words, DATETIME is not standard.
Also, a tiny observation: if you ever create, say, a computed column that will use CAST for date-time conversion, when you script out the table, you will notice that the script will be created using CONVERT, not CAST.
As a final note: 'YYYMMDD' and 'YYMMDD' are ISO standard, so they should be used for data retrieval.

Wednesday, January 27, 2021 - 4:56:06 PM - Zingisa Matwana Back To Top (88110)
Great solution for converting date to an integer " CONVERT(CHAR(8),[MyDate],112) as 'MyDate' " and this solution is efficient than converting date this way " FORMAT([MyDate], 'yyyyMMdd') as 'MyDate' "

Tip helped!

Wednesday, November 25, 2020 - 1:54:12 PM - Greg Robidoux Back To Top (87847)
Hi Jeff, thanks for your input on performance with the FORMAT statement. Good thing there are usually multiple ways to do things in SQL Server.


Wednesday, November 25, 2020 - 10:30:01 AM - Jeff Moden Back To Top (87844)
Good article but FORMAT has been out for quite a while and so have the tests that prove that it's usually about 43 times slower than even some of the strange combinations of multiple CONVERTs. The warning should be that it's a serious performance performance and it should never be used in T-SQL until they fix it. And, please, don't do like the rest of the people have about this and say that "performance sometimes doesn't matter" because it always matters and don't try to justify it to support "migratable" code because easily easily migratable code is a myth.

Tuesday, June 30, 2020 - 5:24:51 AM - Rick Dobson Back To Top (86071)

Helpful and easy to read.  Thanks for your excellent tip.

Rick Dobson

Tuesday, June 16, 2020 - 6:38:53 AM - Tim Cullen Back To Top (86009)

Great tip, Jim!

get free sql tips
agree to terms