SQL Issue: Data Type Conversion(String to DateTime) while Data Loading

Published: 01 January 1970
on channel: TheSantanuGhosh

355

Error: while inserting data from staging table to target table
Conversion failed when converting date and/or time from character string.
Track: Find the records in which the issue exits
Resolution: Solution to fix the data type issue

query to find which records has the issue:
select *
from
(Select top 100 Serial_Number, Name, Admission_Date,
CASE WHEN TRY_CONVERT(datetime, Admission_Date) IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result
from student_staging) a
where Result = 'Cast failed';

Solution: how to fix the data type issue
insert into student
select serial_number, name,
CASE WHEN Admission_Date = ''
THEN NULL
ELSE
(CASE WHEN TRY_CONVERT(datetime, Admission_Date) IS NULL
THEN NULL
ELSE cast ( Admission_Date as datetime)
END )
END AS Admission_Date
from student_staging

On this page of the site you can watch the video online SQL Issue: Data Type Conversion(String to DateTime) while Data Loading with a duration of hours minute second in good quality, which was uploaded by the user TheSantanuGhosh 01 January 1970, share the link with friends and acquaintances, this video has already been watched 355 times on youtube and it was liked by 4 viewers. Enjoy your viewing!

181