In SQL Server, we can use functions like CONVERT() or FORMAT() to convert a valid date type into a format like yyyymmdd.

This format adheres to the ISO 8601 standard, which defines dates to be written as yyyymmdd, or when using delimiters, as yyyy-mm-dd.

In SQL Server, the date type expresses dates in the yyyy-mm-dd format, but we can use the following technique to remove the delimiters and express the date as yyyymmdd.

Convert to a String

Here’s an example of converting the date value to a string in yyyymmdd format:

DECLARE @thedate date = '2035-01-02';
SELECT CONVERT( nchar(8), @thedate, 112 );

Result:

20350102

In this case I used CONVERT() to convert the number to an nchar(8), and I used 112 to determine the format of the resulting string. The value of 112 is used to format the result as yyyymmdd.

That’s all we need to do in order to convert the date to a string in the yyyymmdd format.

Convert to an Integer

If we want to convert the date to an integer in the yyyymmdd format, we can take the above result, and convert it to an integer.

Example:

DECLARE @thedate date = '2035-01-02';
SELECT 
    CONVERT( nchar(8), @thedate, 112 ) AS String,
    CONVERT( int, CONVERT( nchar(8), @thedate, 112 ) ) AS Integer;

Result:

String	        Integer
--------------  --------------
20350102	20350102

Even though the output looks the same, the left column is a string and the right column is an integer.

We can verify this by running the query through the sys.dm_exec_describe_first_result_set() function:

SELECT 
    name AS "Column Name",
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    'DECLARE @thedate date = ''2035-01-02'';
SELECT 
    CONVERT( nchar(8), @thedate, 112 ) AS String,
    CONVERT( int, CONVERT( nchar(8), @thedate, 112 ) ) AS Integer;', 
    null, 
    0);

Result:

Column Name	        system_type_name	max_length	precision	scale
----------------------  ----------------------  --------------  --------------  -----
String	                nchar(8)	        16	        0	        0
Integer	                int	                4	        10	        0

Convert using the FORMAT() Function

As mentioned, we can also use the FORMAT() function to convert dates to another format. This function returns its result as a string, so if we want to further convert it to an integer, we can use the above method for converting the output from FORMAT() to an int type.

See How to Format the Date & Time in SQL Server and How the FORMAT() Function Works in SQL Server for examples of how this function works.