Tutorial :TRY CATCH on a CONVERT in a Select Statement


Is it possible to use TRY CATCH blocks in SQL Selects?

For stuff similar to this for example:

select      order,      CONVERT(DATETIME, orderDate)  from orders  

What's the best way of handling this scenario?


I don't know about try-catch, but in SQL Server you have the ISDATE function and can there for do something like

CASE WHEN ISDATE(orderDate) = 1 THEN CONVERT(DateTime, orderDate) ELSE GETDATE() END  


In MS SQL Server 2012 there is a new construct that does exactly what is asked for:

  SELECT       CASE WHEN TRY_CONVERT(float, 'test') IS NULL       THEN 'Cast failed'      ELSE 'Cast succeeded'  END AS Result;      GO  

See also http://msdn.microsoft.com/en-us/library/hh230993.aspx


In the SELECT clause itself, no.

You can test for a date though using ISDATE()

select      order,      CASE WHEN ISDATE(orderDate) = 1 THEN CONVERT(DATETIME, orderDate) ELSE NULL END  from orders  


I don't think a try catch is possible inside a select, but outside is possible when you're working with stored procedures.

begin try      select cast(strartnr as int) from table  end try  begin catch       select 10000 from table  end catch  


You can use the function ISDATE():

SELECT ISDATE('11/13/2009')  SELECT ISDATE('13/11/2009')  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »