Which values are making the conversion from VARCHAR to DATE fail?

Question

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) AS ClaimDate
FROM MyTable

This leads to the error message:

Conversion failed when converting date and/or time from character string.

I have tried CONVERT(DATE, ClaimDate) as well to no avail.

Now, more than knowing why I get this error, I am curious to know which values are causing this.

I have seen several questions regarding the error message above. But I haven't found the one giving an answer to my problem.

Of course, suggesting a solution in addition to getting bad values is highly appreciated.

Order by Claimdate and make sure all values are greater than A.D. 1754, Dates before that will not convert to datetime. — Robert Sievers, Mar 15 '19 at 15:05
"Should" doesn't mean "does". Use `TRY_CAST` to find which rows contain invalid entries — Panagiotis Kanavos, Mar 15 '19 at 15:05
@RobertSievers they *can*. `select cast('1400-01-01' as date)` works — Panagiotis Kanavos, Mar 15 '19 at 15:06
The fact that you are doing `CAST(ClaimDate AS DATE)` means that you have a "Date" column that isn't storing data as a `date(time)`. That is your *real* problem here. `varchar` is not a one size fits all data type. You need to fix your data type definotions. — Thom A, Mar 15 '19 at 15:07
The real solution is to convert that field to `date` instead of using varchar. Unfortunately, the `yyyy-MM-dd` format is *not* locale agnostic. The way it's parsed is affected by [DATEFORMAT](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql?view=sql-server-2017). The truly agnostic format is `YYYYMMdd` or the full ISO8601 format, `YYYY-MM-dd HH:mm:ss`. If you're absolutely certain there are no `2019-14-03` entries in there, you can use `PARSE(ClaimDate as date USING 'en-GB')` or any other locale except `en-US` — Panagiotis Kanavos, Mar 15 '19 at 15:10
It can happen when trying to convert non-sensible dates such as Feb 30th, and other reasons. — Zorkolot, Mar 15 '19 at 15:50

HoneyBadger · Accepted Answer · 2019-03-15T17:23:22.450

8

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

SELECT ClaimDate 
FROM   MyTable
WHERE  TRY_CONVERT(DATE, ClaimDate ) IS NULL
    AND ClaimDate IS NOT NULL

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!

edited Mar 15 '19 at 17:23

answered Mar 15 '19 at 15:05

HoneyBadger

14,750
3
34
48

Fantastic! Thank you so much! – disasterkid Mar 15 '19 at 15:12
WIll accept as answer as soon as Stack allows me to . – disasterkid Mar 15 '19 at 15:12
@disasterkid are you *sure*? What do these rows contain? Perhaps you're in the US and DATEFORMAT is YMD, which would fail on `2019-03-14`. Ignoring the conversion error will mean that you end up with March 4th instead of April 3rd – Panagiotis Kanavos Mar 15 '19 at 15:14

score 0 · Answer 2 · answered Mar 15 '19 at 15:30

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 as date) is not 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;

select cast('2019-03-14' as date);

While this would throw

set dateformat ydm;

select cast('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 as date) is not null

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

Which values are making the conversion from VARCHAR to DATE fail?

2 Answers2

Linked