Conversion failed when converting date and/or time from character string while converting nvarchar t-CodePudding

I have a table called usda_losses_temp which has a nvarchar(50) type column named end_date which are date values in the format yyyy-mm-dd. I am trying to put this data in a new column called end using the following query:

update usda_losses_temp 
set end = cast((substring(hazard_end_date, 1, 4)   '-'  substring(hazard_end_date, 6, 2)   '-'  substring(hazard_end_date, 9, 2)) as date)

However, I am getting the error that:

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

Example query:

update usda_losses_temp 
set end_date = cast((substring('2020-09-31', 1, 4)   '-'  substring('2020-09-31', 6, 2)   '-'  substring('2020-09-31', 9, 2)) as date)

I also tried

update usda_losses_temp 
set end = FORMAT((substring(hazard_end_date, 1, 4)   '-'  substring(hazard_end_date, 6, 2)   '-'  substring(hazard_end_date, 9, 2)),'yyyy-MM-dd')

but it gave me an error saying:

Argument data type nvarchar is invalid for argument 1 of format function.

I am not able to make it work. How do I fix this?

CodePudding user response：

Your sample date, '2020-09-31', can't be converted to a date, regardless of what kind of formatting or substrings you apply to it. September only has 30 days.

Thus revealing the underlying problem: These never should have been stored as strings in the first place.

To find the offending rows:

SELECT * FROM usda_losses_temp WHERE ISDATE(hazard_end_date) = 0;

Once you've fixed your bad data, you should use the simple CONVERT, col, 120) syntax Dale suggested, instead of all this messy substring goop.

And add a check constraint (CHECK (ISDATE(hazard_end_date)) = 1) until you or they can fix the table. Because that is the real problem here.

CodePudding user response：

Why not just use convert with the correct (120) format?

update usda_losses_temp set [end] =  convert(date, hazard_end_date, 120);

See dbfiddle

If thats not working then you have some invalid dates for the format provided within your data.

CodePudding user response：

Try using convert() instead of cast():

update usda_losses_temp 
set end_date = CONVERT(datetime,(substring('2015-09-29', 1, 4)   '-'  substring('2020-09-29', 6, 2)   '-'  substring('2020-09-29', 9, 2) 'T23:59:59.000'), 126)

Also '2020-09-31' throws me an out-of-range converting exception from varchar. Datetime does implicitly convert to date for date fields.