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?

If IsDate is true, then you can cast it as a datetime. That's the point of IsDate. — cjk, May 28 '09 at 13:56
@JohnIdol: what do you mean by "standard". ISO is of a standard for an example. What are different formats there for orderDate? — dance2die, May 28 '09 at 14:05

score 22 · Accepted Answer · answered May 28 '09 at 13:18

22

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

answered May 28 '09 at 13:18

Robin Day

6

What if the Date Format is different? – Asher Oct 08 '12 at 13:52
My date fields are char(23) and doing ISDATE(datefield) = 1 returns no fields. Their format is yyyy-mm-dd hh:mi:ss.mmm and I guess ISDATE doesn't recognize that. – Zoey Mar 14 '19 at 15:21

score 14 · Answer 2 · answered Aug 16 '12 at 13:50

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

score 3 · Answer 3 · answered May 28 '09 at 13:19

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

answered May 28 '09 at 13:19

gbn

score 2 · Answer 4 · edited Sep 27 '17 at 19:39

2

You can use the function ISDATE():

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

edited Sep 27 '17 at 19:39

C B

answered May 28 '09 at 13:19

A-K

score 1 · Answer 5 · answered May 28 '09 at 13:42

1

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

answered May 28 '09 at 13:42

freggel

5 Answers5