CODEWITHSUNDEEP
sqlsql-server
Brent kolisxh
Brent kolisxh

Reputation: 11

SQL Server : checking the validity of the date

I am trying to get records with only valid date format (YYYYMMDD). I am using the following where condition. I was wondering is there any other way to do this?

    s.Date IS NOT NULL
    AND ISDATE(s.Date) = 1
    AND LEN(s.Date) > 4
    AND s.Date < GETDATE())

Upvotes: 1

Views: 2349

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you want any valid date format, then you can use:

where try_convert(varchar(255), s.date) is not null

If you specifically want YYYYMMDD, then you can do:

where try_convert(varchar(255), s.date, 112) is not null

Upvotes: 0

Edward
Edward

Reputation: 772

I believe you can use the FORMAT() function to achieve this. 

SELECT s.Date 
...
FROM ...
WHERE 
        s.Date IS NOT NULL
    AND ISDATE(s.Date) = 1
    AND s.Date = FORMAT(s.Date, 'yyyymmdd');

But this may be rather slow, as it has to run the FORMAT() function for every record returned for each record that passed the other WHERE conditions.

But you can give it a shot.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

If you have SQL Server 2012 or higher, you can use TRY_CONVERT()

Upvotes: 2

Related Questions