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

Copy Several Tables to a Different Database Using Dynamic SQL

Here is a quick example of how to used dynamic SQL in a cursor to copy several tables from one database to another. I was given a list of 150 tables that one of my customers needed to be copied. I didn’t want to script all of them so I created this cursor. All you need to do is create a list of the tables to be copied.

In this example, I’m using Excel. In my list the SOURCE is the name of the table that we are copying from and the TARGET is where it’s being copied to.

In the SOURCE database, create the following table.

CREATE TABLE [dbo].[TRANSFER_TABLE_LIST](
[SOURCE_TABLE] [varchar](100),
[SOURCE_SCHEMA] [varchar](100),
[SOURCE_DATABASE] [varchar](100),
[TARGET_TABLE] [varchar](100),
[TARGET_SCHEMA] [varchar](100),
[TARGET_DATABASE] [varchar](100)
)
GO

Now, import your table list into the TRANSFER_TABLE_LIST table and create the following cursor.
DECLARE @SQL AS VARCHAR (2000), @SOURCE_TABLE AS VARCHAR (100),
@SOURCE_SCHEMA AS VARCHAR (100), @SOURCE_DATABASE AS VARCHAR (100),
@TARGET_TABLE AS VARCHAR (100), @TARGET_SCHEMA AS VARCHAR (100),
@TARGET_DATABASE AS VARCHAR (100)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT [SOURCE_TABLE]
,[SOURCE_SCHEMA]
,[SOURCE_DATABASE]
,[TARGET_TABLE]
,[TARGET_SCHEMA]
,[TARGET_DATABASE]
FROM [dbo].[TRANSFER_TABLE_LIST]
OPEN c1
FETCH NEXT FROM c1
INTO @SOURCE_TABLE, @SOURCE_SCHEMA, @SOURCE_DATABASE, @TARGET_TABLE, @TARGET_SCHEMA, @TARGET_DATABASE
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'SELECT * INTO [' + @TARGET_DATABASE + '].[' + @TARGET_SCHEMA + '].['
SET @SQL = @SQL + @TARGET_TABLE + '] FROM [' + @SOURCE_DATABASE + '].['
SET @SQL = @SQL + @SOURCE_SCHEMA + '].[' + @SOURCE_TABLE + '];'
EXEC (@SQL)


FETCH NEXT FROM c1
INTO @SOURCE_TABLE, @SOURCE_SCHEMA, @SOURCE_DATABASE, @TARGET_TABLE, @TARGET_SCHEMA, @TARGET_DATABASE
END
CLOSE c1
DEALLOCATE c1

If you receive the following error:

Msg 203, Level 16, State 2, Line 23
The name 'SELECT * INTO [TransCorpReporting].[dbo].[Products_cust_trans] FROM [TransCorp].[dbo].[Products_cust_trans];' is not a valid identifier.

This means that you forgot to add ( ) around the @SQL variable.