Convert Legacy 6 Character and 7 Character CC YY MM DD (ISO) Dates

The dates are stored as Century, Year, Month, and Day, where Century is 0 for 19 and 1 for 20, years are stored as two digits, and months and days always have leading zeros. In this example, somewhere along the way, the leading zeros for the Century were dropped.

Sample Data:


Let’s start solving this by creating our test data.

CREATE TABLE [dbo].[TestDates](
[Row_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Legacy_Date] [varchar](8),
[SQL_Date] [datetime])
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('1000118')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('1000229')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('1000509')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('960426')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('981003')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('950899')
GO

Here is a simple query that will only update valid ISO dates to SQL Server’s datetime data type.

UPDATE dbo.TestDates
SET SQL_Date = CONVERT(VARCHAR(10), CONVERT(DATETIME, CONVERT(VARCHAR(8), Legacy_Date + 19000000)), 101)
WHERE ISDATE(CONVERT(VARCHAR(8), Legacy_Date + 19000000)) = 1

GO