SQL Server Error – Conversion Failed When Converting Date and/or Time from Character String

sql serversql-server-2012

I have a problem, because I have been using this query without problem… until now:

UPDATE T1
SET ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.Date)
FROM FactTransactions T1
INNER JOIN DimStore T2 ON T1.cod_store = T2.cod_storeKey

But now It gave me an error:

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

I have no idea what's going on. Here are the columns:

Ordinal(numeric,null)
Opening_date(varchar, not null)
Date(varchar, not null)
cod_store(int,not null)
cod_storekey(PK,int, not null)

Best Answer

You are storing dates as strings - why? Opening_Date and Date should be date or datetime, not varchar. But before you can fix that, you need to identify the rows that are causing the conversion problem:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE ISDATE([Date]) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE ISDATE(Opening_Date) = 0;

And now that you've fixed the question and I know you're using 2012 and not 2008 R2, it might be cleaner to use TRY_CONVERT(), especially since it will allow you to identify any rows where the month and day are transposed incorrectly. For example, assuming you expect dates to be stored as mm/dd/yyyy strings:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE TRY_CONVERT(datetime, [Date], 101) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE TRY_CONVERT(datetime, Opening_Date, 101) = 0;

In addition to identifying garbage rows where users have stored nonsense like floob and 9992-13-36 as "dates," this will also identify rows where users have stored 13/07/1999 instead of 07/13/1999 (but there is no way to know if 05/06/2000 is meant to be May 6 or June 5).

Now you need to fix those rows before you can correct the tables.

ALTER TABLE dbo.DimStore ALTER COLUMN Opening_Date date; -- or datetime;
ALTER TABLE dbo.FactTransactions ALTER COLUMN [Date] date; -- or datetime;

You might also consider renaming the Date column to be (a) less vague and (b) not a reserved word.

If you can't fix the tables, then you need to change your query:

UPDATE T1
SET ORDINAL = DATEDIFF(DAY, 
  CASE WHEN ISDATE(T2.Opening_Date) = 1 THEN T2.OpeningDate END,
  CASE WHEN ISDATE(T1.[Date]) = 1 THEN T1.Date END)
FROM dbo.FactTransactions AS T1
INNER JOIN dbo.DimStore AS T2 
  ON T1.cod_store = T2.cod_storeKey
WHERE ISDATE(T2.Opening_Date) = 1
  AND ISDATE(T1.[Date]) = 1;

And @RLF brought up a great point, too; if you can't fix the table, then the date columns could contain data that represent a specific date (say, September 7) but be entered in the wrong format (e.g. on a US English system, entered as a string in British format, 7/9/2015). So really, you need to fix the table and stop storing these things as strings.

Some other useful material:

Related Question