I'm trying to migrate data into SQL Server from an existing legacy set of MS Access databases. Currently everything is working except a few core tables that are wide and contain a number of
smalldatetime columns. The problem is an error that SQL Server reports when trying to move these tables:
Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime.
I have a stored procedure which generates dynamic sql to assist with moving each table. I converted all the
datetime tables in SQL Server to
datetime2 columns, and I added a workaround, or so i thought, to deal with this
datetime issue, but it doesn't seem to be working. the stored procedures are kind of bulky so the following is the relevant excerpt:
IF (@Conversion = 1 AND (@ColType LIKE '%date%' OR @ColType LIKE '%time%')) BEGIN SET @ColumnList = @ColumnList + ', IIF(ISDATE([' + @ColName + ']) = 1, [' + @ColName + '], CONVERT(datetime2, ''12/31/1899'')) AS ['+ @ColName + ']' END ELSE BEGIN SET @ColumnList = @ColumnList + ', [' + @ColName + ']' END
Currently, the actual data that is "bad" (can't be converted) is not really important, so I'm trying to substitute it for an obviously invalid date just to get the process to complete without errors. The MS Access frontend has no code to prevent users from entering invalid dates, so for example if someone makes a typo and creates an order with the ship date of "june 2nd, 202", instead of "2015", MS Access will happily store this and then SQL Server fails to convert that value to a
EDIT: To clarify, this procedure is running on a schedule. I can manually repair the bad data now, but if or when more bad data is input by a user, my process will crash again.
As a test, I ran this, and got the same error:
SELECT CONVERT(VARCHAR(10), [DRFINISH], 20) FROM [DB2_JOBS]...[Jobs]
However, this works:
SELECT TOP 100 CONVERT(VARCHAR(10), [DRFINISH], 20) FROM [DB2_JOBS]...[Jobs]
How am I going to convert the data if it causes a crash before it can even be passed into the CONVERT function??
These also crash:
SELECT [DRFINISH] FROM [DB2_JOBS]...[Jobs] WHERE ISDATE([DRFINISH]) = 1 SELECT ISDATE([DRFINISH]) FROM [DB2_JOBS]...[Jobs]
Can anyone tell me what I'm doing wrong? I would appreciate it greatly.