Handling INVALID data in SQL Server 2012

Posted by Bandi under Sql Server category on | Points: 40 | Views : 233
Simetimes, the column data can be improper/incorrect/invalid formats. Most of the times the varchar to datetime; varchar to int; int to varchar convertions results the exceptions such as 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value'...

Best approach to handle this kind of exceptions in the applications is to make use of TRY_CONVERT() function in SQL Server 2012.

TRY_CONVERT() doesn't throws any exception if incompatible convertions occur; simply it returns NULL if the incorrect format data exists in table.

Select '2015MAR1 To DateTime' As Data , TRY_CONVERT(DATETIME,'2015MAR1') As Value  
Select '2015-MAR-32 To DateTime' As Data , TRY_CONVERT(DATETIME,'2015-MAR-32') As Value

OutPut is
Data	                Value 
2015MAR1 To DateTime 2015-03-01 00:00:00.000

Data	                Value 
2015-MAR-32 To DateTime NULL

The second statement throws exception in case we use CONVERT function of SQL Server 2008. TRY_CONVERT is introduced in SQL server 2012.

32nd March, 2015 is INVALID date, so TRY_CONVERT() function returns NULL instead of exception.

Comments or Responses

Login to post response