Tutorial :TRY CATCH on a CONVERT in a Select Statement



Question:

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?


Solution:1

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  


Solution:2

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


Solution:3

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  


Solution:4

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  


Solution:5

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
Previous
Next Post »