Search code examples
sqlsql-serverdaterdbms

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

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.

enter image description here

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!

Solution

  • 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.