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

xoshrz7s  于 2023-08-02  发布在  其他
关注(0)|答案(3)|浏览(185)

I have 3 tables in the database that I'm working on. Out of 3, two of the tables have columns that include dates. When I checked the information schema of the columns I found that dates have the wrong data type. If you see the picture below, the highlighted columns should be stored as DATE data type.

So, I used the following query to change their data type from varchar to DATE :

ALTER TABLE [dbo].[Customer]
ALTER COLUMN DOB DATE;

ALTER TABLE [dbo].[Transactions]
ALTER COLUMN tran_date DATE;

The error that I get is:

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

Please let me know how I can fix this error. Thanks!

x7rlezfr

x7rlezfr1#

What you can do is update the value using try_convert() first and then alter the column name. Note: This will set any invalid values to NULL .

update customer
    set dob = try_convert(date, dob);

alter table customer alter column dbo date;

If you want to see the bad values, then before you change the table, run:

select c.*
from customer c
where try_convert(date, dob) is null and dob is not null;

You may have other ideas on how to fix the values.

vd8tlhqk

vd8tlhqk2#

You can't change from varchar to date or time or datetime by altering the column. Why? Because SQL Server does not know if the field contains '1/1/2020' or 'My dog is cute'. You will have to rebuild the table with the proper data types and then CAST() or CONVERT() the values to a true date time.

Underneath the hood, this makes more sense. A char/varchar uses one byte per character. nchar/nvarchar uses 2 bytes per character. A datetime is a number, not a character. This means you need a routine that changes this into the correct number. If you want to get deeper, the number is the number of ticks (nanoseconds) since midnight on January 1, 0001 in the Gregorian Calendar. More info .

plicqrtu

plicqrtu3#

You can use the ISNULL function in SQL to convert an empty string to a null DateTime value The NULLIF function is used to return NULL if the value in date_column is an empty string. The ISNULL function then takes the result of NULLIF and replaces it with NULL if it is still NULL.

UPDATE table_name
SET date_column = ISNULL(NULLIF(date_column, ''), NULL);

相关问题