I have a table with DateFrom column

it's an nvarchar col in the col I have data like that

01
02
10/10/2020
04 

some strings and some DateTime values I need to cast it to DateTime but only if it's a date if not then pull out the value like it is can is this possible? thanks ...

CodePudding user response：

a Simple Try cast should do the job here

DECLARE @Table TABLE (Val NVARCHAR(20))

INSERT INTO @Table
VALUES('1'),('2'),('10/10/2020'),('04')


SELECT
    *,
    TRY_CAST(Val AS DATE)
    FROM @Table

Results

CodePudding user response：

Something like this should work. It uses a CASE statement to check whether or not the value is a valid date and if so casts it to DATETIME, then converts it to VARCHAR (so dates and other values can be returned as the same column).

You can find other datetime to string styles here if you need them formatted differently:

CodePudding user response：

You can easily check this with the case statement. Postgres example

select case when '12/12/2021' ~ '\d{2}\/\d{2}\/\d{4}' then 'this is the date' ELSE 'this is a string' end;

->this is the date

select case when 'some text' ~ '\d{2}\/\d{2}\/\d{4}' then 'this is the date' ELSE 'this is a string' end;

->this is a string

so you can use it like this

select case when fild_01 ~ '\d{2}\/\d{2}\/\d{4}' then TO_DATE(fild_01,'DD/MM/YYYY')::text ELSE fild_01 end from test_table;

as you can see, case construction can't generalize two different types of data, so I had to convert them to the same type of text

CodePudding user response：

Use the TRY_CONVERT function, with style 107

SELECT TRY_CONVERT(datetime, 'Jun 27 2021 12:22AM', 107)

This returns a null if the value is not a date

CodePudding user response：

You can choose according to your needs.

When you want to set default value:

SELECT IIF(ISDATE(Val) = 1 , Val,'YOUR_DEFAULT_VALUE')

Whent default value not matter (It will be NULL):

SELECT TRY_CAST(NULL AS DATE)