SQL Convert Date to YYYYMMDD (2024)

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

Problem

Often when working withdates inSQL 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 Datein a sortable format. This format can be used when you do not want to showthe delimiter between the year, month, and day. This is a good option whenlooking for a consistent standard for an international audience and you do not needto include the time portion of a date stamp. However, when using this formatas a filter there may be a gotcha for beginners that you should be aware of.

Solution

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 theCAST and CONVERTfunctions 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 sampledata. 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) ON [PRIMARY];GO

Load Sample Test Data

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

SET NOCOUNT ON;-- Declare ParametersDECLARE @SetDateTime DATETIME = '2020-01-01 14:04:03.230'; -- current date or GETDATE()INSERT INTO [dbo].[TestDate] ([MyDate],[CharDate],[MyDateTime])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] ([MyDate],[CharDate],[MyDateTime])SELECT @SetDateTime, Convert(CHAR(8),@SetDateTime,112), @SetDateTimeGOINSERT INTO [dbo].[TestDate] ([MyDate] ,[CharDate], [MyDateTime])SELECT top 1 [MyDateTime]+1, Convert(CHAR(8),[MyDateTime]+1,112), [MyDateTime]+1FROM [dbo].[TestDate]ORDER BY 1 desc GO 20 --load 20 days of dates

Results: Example SQL Server dates loaded.

Convert Dates to Char 'yyyymmdd'

Next, converting a DATE and DATETIME datatype to character 8 'yyyymmdd'output usingCONVERT andFORMAT functions.

--A. CONVERT use style = 112 to CHAR 8 or NCHAR 8SELECT 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 conversionto 'yyyymmdd' regardless of the format or the conversion method. Theresults below were reduced to 3 rows each for the demo.

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

Now, convert the Character format 'yyyymmdd' to a Date and DateTimedata 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.

Filtering Dates by Char 'yyyymmdd' and the Gotcha

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

-- Test 'YYYYMMDD' filter against Date datatypes--A. DATE datatypeSELECT [MyDate]FROM [dbo].[TestDate]WHERE [MyDate] = '20200101'--B. DATETIME datatypeSELECT [MyDateTime]FROM [dbo].[TestDate]WHERE [MyDateTime] = '20200101' --implicit conversion to datetime Midnight!;

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

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

When using the 'yyyymmdd' in the filter against DateTime datatype youmust 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:SELECT *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 SELECT *FROM [dbo].[TestDate]WHERE [MyDate] = 20200101;

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

Alternative Formats

Last, I will share examples of alternative formats for returning dates.Also, I will show how robust aSQLSELECT statement is at converting the other formats when used ina 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.SELECT *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! Notethe different date delimiters and the ability to use different delimited dates asfilters.

Wrap Up

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

Next Steps

• Go more in depth withDate and Time Conversions Using SQL Server
• Read aboutSQL Datetime Best Practices
• Check out all of theSQLServer Date Tips
• Find other articles on Datetime - MSSQLTips search on Datetime
• More SQL Server Date Functions for different formats and calculations
  • Add and Subtract Dates using DATEADD in SQL Server
  • DATEDIFF SQL Server Function
  • Determine SQL Server Date and Time Parts with DATEPART and DATENAMEFunctions
  • SQL Server Date and Time Functions with Examples
  • SQL CONVERT Date Function
  • SQL Date Format examples using SQL FORMAT Function
  • How SQL Server handles the date format YYYY-MM-DD
  • SQL Server Date and Time Tips

About the author

Article Last Updated: 2023-10-31