How to identify conversion issues using TRY_CAST ()

A common issue with the ELT process is that you find out errors after they have been loaded. This is usually not any issue unless the database specifically doesn’t have an option to help identify the error specifically. An example of this is the relief expressed by developers when Microsoft announces the release of the Verbose Truncate messages.

Previously we would only know a truncation error will occur not exactly which row or data is the cause of it. This was fixed by expressing in the error message the exact value that was causing the issue. However another more frequently faced issue was conversion errors. Those familiar with SSIS will attest that it redirects rows for both Truncation and Conversion errors.

In SQL Server we can identify the truncate errors by looking for the error message itself. Finding conversion issues is slightly different since you need to call the TRY_CAST, TRY_COVERT functions on the columns you want to troubleshoot.

As demonstrated below:-

create table datedata 
( id int identity(1,1) ,
DateasString varchar(100))

go

insert into datedata 
select '04/04/2020' union 
select '04/04.2020'

go

select * from datedata 
where isdate(DateasString) =1

select cast(dateasstring as date) from datedata

select * , try_cast( dateasstring as date ) from datedata 
go

select * , try_convert(date ,  dateasstring  ) from datedata 

go

select * from datedata 

This was an issue I recently faced when loading some data from a client system. The source data is highly prone to errors. As a result we had to store all data in varchar data types in staging and cleanse the data before the final load.

Often we would get issues with date format etc. and this was addressed by using the TRY_CAST function.