CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Pawan Nogariya
Pawan Nogariya

Reputation: 8950

SQL Server - IsDate function returning unexpected result

I am trying to do something like this 

when ISDATE(u.NewValue)=1 then CONVERT(varchar,cast(u.NewValue as date),23)

But sql server returns 1 for 2406 also as it is a date, so this

select ISDATE('2406')

returns 1.

Which is not the case. And because of this I get the output like this 2406-01-01 which is wrong.

What should be the way to check if the value is actaully a date, when my data will be a date it will come in this format 2019-05-28 07:59:04.450, so I want to capture only this type of data as date.

Can anyone suggest me the approach for this

Upvotes: 0

Views: 189

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Well, you can use like:

select (case when col like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
             then convert(datetime, col)
        end)

Depending on what your column looks like, you can relax the formatting requrements:

select (case when col like '%-%-% %:%:%.%'
             then try_convert(datetime, col)
        end)

I am surprised that SQL Server converts this to a date just using the year:

try_convert(date, '2019', 121)

The format can be incomplete.

Upvotes: 2

Related Questions