When working with SQL Server, if we’re given a number that represents a date in the yyyymmdd format, we can use functions like CAST() or CONVERT() to convert that number to a valid date type. This will enable us to perform date operations against it that we might not be able to do when it’s still in numeric form.

Example

Here’s an example to demonstrate:

SELECT CAST( CAST( 20281030 AS char(8)) AS date );

Result:

2028-10-30

In this case I used CAST() to convert the number to a char(8), then I used another CAST() to convert that char(8) to a date. The reason for this is because SQL Server doesn’t allow an explicit conversion from the int type to the date type. But it does allow us to convert an int to a string, and then that string to the date type.

Here’s an example of what happens when we try to directly convert the int to a date:

SELECT CAST( 20281030 AS date );

Result:

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to date is not allowed.

SQL Server tells us that the conversion is not allowed.

But here’s what happens when we pass the number as a string literal:

SELECT CAST( '20281030' AS date );

Result:

2028-10-30

In this case it successfully converted the value to a date. That’s because SQL Server allows us to convert the string to a date. In this example we provided a string literal, whereas in the first example we explicitly converted the int to a char(8) string.

Convert YYYYMMDD to datetime

It didn’t necessarily have to be the actual date type that I converted to. I could have converted it to another date/time value such as datetime or datetime2. In such cases, a time component would be added, set to all zeros (e.g. 00:00:00.000 for datetime, and 00:00:00.0000000 for datetime2).

Here’s an example of converting our yyyymmdd number to a datetime2 type:

SELECT CAST( CAST( 20281030 AS char(8)) AS datetime2 );

Result:

2028-10-30 00:00:00.0000000

As expected, a whole bunch of zeros are added.

Format YYYYMMDD to MM/DD/YYYY

One possible reason for converting the yyyymmdd number to an actual date type might be to format the date. For example, we might want to format the date as mm/dd/yyyy.

We can use the following query to do that:

SELECT CONVERT( nvarchar, CAST( CAST( 20281030 AS char(8)) AS date), 101 );

Result:

10/30/2028

Here I added the CONVERT() function to do another conversion. I used CONVERT() because this function allows us to add an argument to specify the style of the resulting date. This determines the format that the date is returned. A value of 101 returns the date as mm/dd/yyyy.

Note that by doing this, we need to convert the date to a string. That’s because date types don’t support string characters like / and other formatting niceties we might impose on the date. Converting it to a string enables us to apply formatting to the date so that it’s easier for humans to read.

Another way to format dates is with the FORMAT() function. One benefit of this function is that it allows us to specify a locale to use for the date/time format. That way we don’t need to remember the format of that locale. See How to Format the Date & Time in SQL Server for more information and examples.