4

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)
  • 4
    You should be using Date (or Datetime2) type for Opening_date and Date in order to avoid this type of problem. The format of one of your string may be wrong. – Julien Vavasseur Oct 22 '15 at 11:53
6

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:

  • Thank you @Aaron Bertrand. DimDatel structure is the same than AdventureWorksDW2008, It uses the PK as a string and the AlternativeDateKey as a Date. I have also configurated Date column from Factransaction as a string, because I update this table from another one, and I cannot change it. It's YYYYMMDD. I'll try to use you last query because I cannot change my structure model now – Miguel Oct 22 '15 at 13:53
5

You can use Convert with the correct style in order to convert varchar(x) in your local format to proper dates:

DATEDIFF(DAY, convert(date, T2.Opening_Date, 104), convert(date, T1.Date, 104))

In this sample, I supposed you are using the German style (dd.mm.yyyy). German style is 104:

Select convert(date, '01.02.2015', 104)

Ouput: 2015-02-01

You must adapt it to your settings and local format and replace 104 by whatever your are using. Main style are:

Style   Standard        Input/Ouput
1       U.S.            1 = mm/dd/yy
101     U.S.            101 = mm/dd/yyyy
2       ANSI            2 = yy.mm.dd
102     ANSI            102 = yyyy.mm.dd
3       British/French  3 = dd/mm/yy
103     British/French  103 = dd/mm/yyyy
4       German          4 = dd.mm.yy
104     German          104 = dd.mm.yyyy
5       Italian         5 = dd-mm-yy
105     Italian         105 = dd-mm-yyyy

Look at CAST and CONVERT (Transact-SQL)

If you have dates with bad formats or mixed formats, you can also look at TRY_CONVERT with a query such as:

Select ... 
FROM FactTransactions T1
INNER JOIN DimStore T2 ON T1.cod_store = T2.cod_storeKey
Where try_convert(date, T2.Opening_Date, 104) is null 
or try_convert(date, T1.Date, 104) is null

It will give you a list of dates with incorect format for style 104. Again, adapt it to your settings and replace 104. You can use it before fixing bad formats and dates.

But the main question and problem are: why are you using varchar(x). You should be using Date or Datetime 2 if you can update your table.

  • 1
    Yes, TRY_CONVERT() is cleaner and more reliable than ISDATE(). I used the latter because of the original title in the question (2012R2) - I wasn't sure if they really meant 2008 R2, where TRY_CONVERT() was not available. – Aaron Bertrand Oct 22 '15 at 13:32
  • I was not looking at the title and miss the R2 part. The tag being 2012, I guess it may be fine... – Julien Vavasseur Oct 22 '15 at 13:36
  • Yes, I know, I wasn't criticizing your answer, I was defending why I didn't use the superior TRY_CONVERT(). – Aaron Bertrand Oct 22 '15 at 13:37
  • No worries. It's best to have both options on the answers and show under which circumstances they are better/available. – Julien Vavasseur Oct 22 '15 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.