Hi,

In the below scenario we are inserting some time related fields in Temp table.But its data type is varchar. Once data loading is finished in the temp table (Data is loading by source team SQOOP and they are unable to load if the source datatype is having Date or datetime) we have to alter the column datatypes. somehow, some character data in inserted in date columns (look at into 3rd insert statement). while altering the table it is failing. Can we do any alternative for this (Means if any varchar data that is non convertible to date can we make as null)

INSERT INTO ##TEMP_TEST
SELECT '2014-09-30','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM'
UNION SELECT '2014-09-29','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM'
UNION SELECT '2014-09-28','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM'
GO
INSERT INTO ##TEMP_TEST SELECT NULL,NULL,NULL,NULL 
GO
INSERT INTO ##TEMP_TEST SELECT 'A','B','C','D'

go

ALTER TABLE ##TEMP_TEST ALTER COLUMN CAREPACK_ST_DT DATE
GO
ALTER TABLE ##TEMP_TEST ALTER COLUMN CAREPACK_END_DT DATE
GO
ALTER TABLE ##TEMP_TEST ALTER COLUMN INS_GMT_TS DATETIME2
GO
ALTER TABLE ##TEMP_TEST ALTER COLUMN UPD_GMT_TS DATETIME2
GO

Please let me

If you use SQL Server 2012, look at TRY_CONVERT function.

SELECT TRY_CONVERT(INT, 100) AS try1, TRY_CONVERT(INT, 'abc') AS try2;

Hi ,

We are using SQL Server 2008 SP3.