Removing or Replacing Bad Dates in Microsoft SQL

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s