CODEWITHSUNDEEP
t-sqlazure-sql-database
Dario Federici
Dario Federici

Reputation: 1258

tsql convert string into date when possible

I've got a column to import into an Azure SQL DB that is supposed to be made of dates only but of course contains errors.

In TSQL I would like to do something like: convert to date if it's possible otherwise null.

Does anyone know a statement to test the convertibility of a string into a date?

Upvotes: 1

Views: 899

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175934

You could use TRY_PARSE:

Returns the result of an expression, translated to the requested data type, or null if the cast fails. Use TRY_PARSE only for converting from string to date/time and number types.

SELECT TRY_PARSE('20129901' AS DATE)
-- NULL

Additionaly you could add culture:

SELECT TRY_PARSE('10/25/2015' AS DATE USING 'en-US')

And importing:

INSERT INTO target_table(date_column, ...)
SELECT TRY_PARSE(date_string_column AS DATE) ...
FROM source_table
...

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28920

use TryCast or Isdate

select 
try_Cast('test' as date)
select try_Cast('4' as  date) 

select case when ISDATE('test')=1 then cast('test' as date) else null end

TryCast will fail if the expression is not in expected format ..ie.,if the explicit conversion of expression is not permitted 

select 
try_cast( 4 as xml)
select try_Cast(4 as date)

Upvotes: 2

Related Questions