Is there a safe way to cast SQL datetimes?

Pretty simple question that I can't quite find the answer f开发者_如何转开发or:

Is there a simple and safe† way to cast a varchar to datetime in SQL Server?

† i.e. gracefully handle non-datetime strings with a default datetime value

You can filter your rows using the isdate function. This query returns three rows without any conversion errors:

with v as (
    select '20110714' value union all
    select '2011-07-15' union all
    select '3/22/2011' union all
    select 'foo' 
)
select cast(value as datetime)
from v
where isdate(value) = 1

Edit

When you want a default vaue (like the current date/time), you could do something like this:

select case when isdate(value) = 1 then cast(value as datetime) else getdate() end

From SQL Server Denali you can use TRY_CONVERT. A case expression is the only safe way in previous versions.

You could try

  select cast ('28/08/2006 11:23:25' as datetime)

SELECT * FROM tab_val WHERE (CASE ISDATE(val) WHEN 1 THEN CAST(val As DateTime) ELSE NULL END) 

More details in the manual

we can place a check that column have the valid datetime and if not then return nulls not genrating any error.

SELECT CASE ISDATE([YourDate]) WHEN 0
    THEN CAST([YourDate] AS DATETIME)
    ELSE CAST(NULL AS DATETIME) END AS [Your Date]
    FROM [dbo].[yourtable]