Convert Integer to Date in SQL Server: ddMMyyyy Values

To convert integer to date values such as 09122020, split the number into day, month and year. Then build the date from the three parts. The traps are a lost leading zero, the session language and a value that is not a real date. This post tests four methods against all three.

Why the Zero Disappears

An integer has no leading zeros. The literal 09122020 is the number 9,122,020, and that is what a table stores. A column that holds ddMMyyyy dates as integers therefore mixes eight digit and seven digit values. Any method that counts characters from the left breaks on the early days of a month.

The demo database is IntDateDemo. The table holds six shipments. The last two rows are not real dates. Row 5 is 31 April, and row 6 is zero. Run it on a test server.

IF DB_ID(N'IntDateDemo') IS NULL CREATE DATABASE IntDateDemo;
GO
USE IntDateDemo;
GO
DROP TABLE IF EXISTS dbo.Shipments;
CREATE TABLE dbo.Shipments (ShipmentID int NOT NULL PRIMARY KEY, ShipDateInt int NOT NULL);
INSERT INTO dbo.Shipments (ShipmentID, ShipDateInt)
VALUES (1, 09122020), (2, 31122020), (3, 1012021), (4, 29022020), (5, 31042020), (6, 0);
SELECT ShipmentID, ShipDateInt FROM dbo.Shipments ORDER BY ShipmentID;

Row 1 shows 9122020, with no zero. Rows 3 and 4 show the same effect for 1 January and 29 February.

Method One: Split With Arithmetic

Integer division and the modulo operator cut the number into parts. The year is the remainder after dividing by 10,000. The month sits in the middle, and the day is everything above 1,000,000. DATEFROMPARTS builds the date from them. The zero never matters, because the arithmetic does not use characters.

SELECT ShipmentID, ShipDateInt,
       DATEFROMPARTS(ShipDateInt % 10000, ShipDateInt / 10000 % 100, ShipDateInt / 1000000) AS ShipDate
FROM dbo.Shipments
WHERE ShipmentID <= 4
ORDER BY ShipmentID;

All four are right, including the leap day. Now remove the filter. DATEFROMPARTS has no mercy for an impossible date, and one bad row fails the whole query.

SELECT ShipmentID,
       DATEFROMPARTS(ShipDateInt % 10000, ShipDateInt / 10000 % 100, ShipDateInt / 1000000) AS ShipDate
FROM dbo.Shipments;

The query stops with Msg 289. The text reads as follows.

Cannot construct data type date, some of the arguments have values which are not valid.

Method Two: Pad, Format and TRY_CONVERT

When bad values exist, use a conversion that returns NULL instead of an error. Pad the number to eight digits with zeros and insert the slashes with STUFF. Then convert with style 103, which reads dd/mm/yyyy. TRY_CONVERT, DATEFROMPARTS, CONCAT and FORMAT need SQL Server 2012 or later.

SELECT ShipmentID, ShipDateInt,
       TRY_CONVERT(date, STUFF(STUFF(RIGHT(CONCAT('0000000', ShipDateInt), 8), 3, 0, '/'), 6, 0, '/'), 103) AS ShipDate
FROM dbo.Shipments
ORDER BY ShipmentID;

The bad rows come back as NULL, and the good rows are unchanged. The padding is what saves rows 1 and 3. Without it, the character positions are wrong.

You cannot filter on the alias ShipDate in the same SELECT. Wrap the query in a CTE and filter on ShipDate IS NULL to list the values to fix.

WITH Converted AS (
    SELECT ShipmentID, ShipDateInt,
           TRY_CONVERT(date, STUFF(STUFF(RIGHT(CONCAT('0000000', ShipDateInt), 8), 3, 0, '/'), 6, 0, '/'), 103) AS ShipDate
    FROM dbo.Shipments
)
SELECT ShipmentID, ShipDateInt FROM Converted WHERE ShipDate IS NULL ORDER BY ShipmentID;

Two Methods That Break

The first is a version that does the STUFF without the padding. It works for 31122020 and fails for 9122020, because the text is one character short.

SELECT CONVERT(date, STUFF(STUFF(CONVERT(char(8), ShipDateInt), 5, 0, '/'), 3, 0, '/'), 103) AS ShipDate
FROM dbo.Shipments WHERE ShipmentID = 1;

It fails with Msg 241, a conversion error. The second is the FORMAT version. It formats the number as 9-12-2020 and lets the session language decide what the text means. Check the language first, then run it.

SELECT @@LANGUAGE AS CurrentLanguage,
       CAST(FORMAT(ShipDateInt, '##-##-####') AS date) AS ShipDate
FROM dbo.Shipments WHERE ShipmentID = 1;

On a us_english session the result is 2020-09-12. That is 12 September, not 9 December. Nothing fails, and the answer is wrong. For row 2 the same call fails with Msg 241, because 31 is not a month. A British session reads the text the other way and gets row 1 right. A method whose answer depends on the language of whoever runs it is a bug waiting for a new login.

You can see the effect on the text alone. The batch below reads the same text under the default setting. Then it reads it again with day first. The setting lasts only for the dynamic batch.

SELECT CAST('9-12-2020' AS date) AS DefaultReading;
EXEC (N'SET DATEFORMAT dmy; SELECT CAST(''9-12-2020'' AS date) AS WithDayFirst;');
SELECT CONVERT(date, '9-12-2020', 105) AS Style105;

Style 105 means dd-mm-yyyy in every language. That is why the padded method above is safe: its style fixes the order.

What the Methods Cost

On a million rows, the cost differs. The script below builds a table of one million ddMMyyyy values. It takes the largest converted date with each of three methods. The time appears in the Messages tab.

DROP TABLE IF EXISTS dbo.BigShipments;
CREATE TABLE dbo.BigShipments (ShipmentID int NOT NULL PRIMARY KEY, ShipDateInt int NOT NULL);
INSERT INTO dbo.BigShipments (ShipmentID, ShipDateInt)
SELECT x.n, DAY(y.d) * 1000000 + MONTH(y.d) * 10000 + YEAR(y.d)
FROM (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c) AS x
CROSS APPLY (SELECT DATEADD(day, x.n % 3650, '2015-01-01') AS d) AS y;
GO
SET STATISTICS TIME ON;
DECLARE @d date;
SELECT @d = MAX(DATEFROMPARTS(ShipDateInt % 10000, ShipDateInt / 10000 % 100, ShipDateInt / 1000000))
FROM dbo.BigShipments OPTION (MAXDOP 1);
SELECT @d = MAX(TRY_CONVERT(date, STUFF(STUFF(RIGHT(CONCAT('0000000', ShipDateInt), 8), 3, 0, '/'), 6, 0, '/'), 103))
FROM dbo.BigShipments OPTION (MAXDOP 1);
SELECT @d = MAX(TRY_CAST(FORMAT(ShipDateInt, '##-##-####') AS date))
FROM dbo.BigShipments OPTION (MAXDOP 1);
SET STATISTICS TIME OFF;

The arithmetic is the fastest. The padded text costs about three times as much, and FORMAT costs more than ten times. To convert integer to date on clean data, use the arithmetic. Use the padded text when the data is not clean. Never put FORMAT in a conversion of many rows. On a us_english session, the FORMAT query also returns NULL for every date with a day above 12. Its answer is wrong as well as slow.

One More Case and the Better Fix

If the integer is yyyyMMdd, a single style converts it. Style 112 reads that layout directly.

SELECT CONVERT(date, CAST(20201209 AS char(8)), 112) AS ShipDate;

You could argue that you should not convert these values at all. The better fix is a date column. Add a column of type date and fill it with the padded method. Check the NULL rows by hand, then retire the integer. A real date type validates every value and sorts correctly.

What to Remember

Convert integer to date with arithmetic and DATEFROMPARTS when the data is clean. Use padding, STUFF and TRY_CONVERT with style 103 when bad values exist. Skip FORMAT for conversions. When you finish the demo, drop the database.

USE master;
GO
DROP DATABASE IntDateDemo;

A date is not a number with a pattern, it is a value that has one reading.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.