Converting date format gives "Conversion failed when converting date and/or time from character string"

Question

The date format in the table is YYYYMMDD and I would like to convert it to the following format but it is failing with an error:

2019-07-23 00:00:00.000

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

Here is the statement I'm using:

convert(varchar(10), convert(datetime, InstallDate0), 23)

Answer 1

The real problem is the choice of your datatype. varchar is the wrong choice. As a result, it seems that you now have some rows where the value of the "date" has been lost, as it can't be converted to a date.

To properly fix this problem, fix your datatype. Firstly I would create a new column to store the bad values:

ALTER TABLE YourTable ADD BadDate varchar(20); --as it's yyyyMMdd you don't need more than 8 characters, but we'll assume you have some really bad values
UPDATE YourTable 
SET BadDate = InstallDate0
WHERE TRY_CONVERT(datetime,InstallDate0) IS NULL;

Now that you've done that, time to update the existing column:

UPDATE YourTable
SET InstallDate0 = CONVERT(varchar(8),TRY_CONVERT(datetime, InstallDate),112);

This'll set every value to the yyyyMMdd format where the value can be converted. NOw you can alter your table:

ALTER TABLE YourTable ALTER COLUMN InstallDate0 date; --AS it's yyyyMMdd, it seems silly to actually use datetime

Now you have a proper datetime column.

You'll then need to inspect the values of BadDate and try to correct them (or admit that any information they held has been lost for ever).

If you "must" have another column with the format, then add a further column:

ALTER TABLE YourTable ADD InstallDate0_f AS CONVERT(varchar(23),InstallDate0,121);

Answer 2

You can determine where the problems are using TRY_CONVERT(). The problem would seem to be the conversion to a datetime, so try this:

select InstallDate0
from t
where try_convert(datetime, InstallDate0) is null;