Which Values Are Making The Conversion From Varchar To Date Fail?

I am reading a value from a key/value table that should contain a NULLABLE date with the format yyyy-MM-dd. The simplified version of the query is. SELECT CAST(ClaimDate AS DATE) A

Solution 1:

Depending on your SQL Server version (2012+), you can use TRY_CONVERT:

SELECT ClaimDate 
FROM   MyTable
WHERE  TRY_CONVERT(DATE, ClaimDate ) ISNULLAND ClaimDate ISNOTNULL

This will give you the values that can't be converted to a date, how you're going to solve those issues is another question. Also, a value of 2019-01-12 may be converted, but if it's in a string you cannot be sure whether this is a date in December or in January. You may get valid but wrong dates!

Solution 2:

As others explained, the real problem is using varchar instead of date as the type. This allowed bad data to be inserted in the field. Fixing this may not be possible - what if there are strings using different date formats in different rows?

CAST(ClaimDate AS DATE) can fail if the text field contains a non-date string. In this case, the rows can be ignored with :

SELECT ClaimDate AS ClaimDate
FROM MyTable
where TRY_CAST(ClaimDate asdate) isnot null

It's fortunate that the type is date. datetime parsing is affected by the DATEFORMAT setting. If it was set to YDM, this line would still work :

set dateformat ydm;

selectcast('2019-03-14'as date);

While this would throw

set dateformat ydm;

selectcast('2019-03-14'as datetime);

Things would be a lot worse if the value was 2019-03-04. Instead of throwing, the cast would return the wrong date.

Fortunately, from DATEFORMAT's remarks :

The DATEFORMAT ydm isn't supported for date, datetime2, and datetimeoffset data types.

That's why the failing data should be checked first with

SELECT ClaimDate AS ClaimDate
FROM MyTable
where TRY_CAST(ClaimDate asdate) isnot null

If everything is OK, the type should be changed to date to ensure bad values can't be inserted