Sometimes you receive data from other sources that are suppose to be dates but are not entered correctly. The roman classic I was born in ’12/15/0010′, or the time traveler ’03/23/99999′. Sometimes its just junk like ‘tbd’ etc. This is where try_cast or try_convert is essential.
Try_Cast or Try_Convert has been available since SQL 2008. These will prevent an error to stop your query.
select TRY_CAST('1/1/2016' as date)
select TRY_CONVERT(date,'1/1/2016')
These will both successfully return a date of 1/1/2016. But if you try to process the following:
select try_Convert(date,'1/41/2016')
This will return a null instead of an error. You can wrap the try with a coalesce and return a default value
select coalesce(try_Convert(date,'1/41/2016'),'1/1/1900')
This will return 1/1/1900 and your query completes.
sqlriley 