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

Question

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!

That means some of your data cannot be implicitly converted to a date - one of the big reasons never to use a varchar in the first place. You have to correct the data before you can change the column datatype. Try using try_convert to find the bad data. — Dale K
– Dale K, Commented Dec 28, 2020 at 20:06
Hopefully this has taught you one of the many reasons why varchar is the wrong datatype choice for a date and time value. — Thom A
– Thom A ♦, Commented Dec 28, 2020 at 20:12

Gordon Linoff · Accepted Answer · 2020-12-28 20:06:27Z

1

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.

answered Dec 28, 2020 at 20:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2020-12-28 20:12:42Z

1

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.

edited Dec 28, 2020 at 20:12

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Dec 28, 2020 at 20:11

Gregory A Beamer

17k3 gold badges27 silver badges32 bronze badges

2 Comments

Dale K Over a year ago

Well you can, but SQL Server will do an implicit conversion if it can, which might not be what one wants.

Gregory A Beamer Over a year ago

@Dale K It depends on how you are doing it as well. SSMS restricts you fairly well from shooting yourself in the foot since 2012, maybe 2008. Prior to that, you could do this type of thing, which could really f*** up your database. :D

bostonsqd · Accepted Answer · 2023-07-20 15:53:37Z

0

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);

answered Jul 20, 2023 at 15:53

bostonsqd

1161 silver badge6 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related