I have a problem, due to the fact that I have been using this query there is no problem... Until now:

UPDATE T1SET ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.Date)FROM FactTransactions T1INNER join DimStore T2 ~ above T1.cod_store = T2.cod_storeKeyBut now It provided me an error:

Conversion failed when converting day and/or time from personality string

I have no idea what"s walking on. Here are the columns:

Ordinal(numeric,null)Opening_date(varchar, no null)Date(varchar, not null)cod_store(int,not null)cod_storekey(PK,int, not null)
sql-server sql-server-2012
improve this concern
edited Oct 22 "15 in ~ 17:46


Aaron Bertrand
175k2525 gold badges374374 silver badges580580 bronze title
request Oct 22 "15 in ~ 11:43

33522 yellow badges55 silver- badges1313 bronze title
include a comment |

2 answers 2

energetic oldest Votes
You room storing dates as strings - why? Opening_Date and Date have to be date or datetime, not varchar. But prior to you have the right to fix that, you need to identify the rows the are leading to the conversion problem:

SELECT cod_store, FROM dbo.FactTransactionsWHERE ISDATE() = 0;SELECT cod_storekey, Opening_DateFROM dbo.DimStoreWHERE ISDATE(Opening_Date) = 0;And currently that you"ve addressed the question and also I know you"re making use of 2012 and not 2008 R2, it can be cleaner to usage TRY_CONVERT(), especially due to the fact that it will enable you to identify any rows wherein the month and day room transposed incorrectly. Because that example, assuming you expect days to it is in stored together mm/dd/yyyy strings:

Now you must fix those rows before you can correct the tables.

You are watching: Conversion failed when converting date and/or time from character string

ALTER TABLE dbo.DimStore change COLUMN Opening_Date date; -- or datetime;ALTER TABLE dbo.FactTransactions alter COLUMN date; -- or datetime;You might also consider renaming the date column to be (a) much less vague and also (b) no a scheduled word.

If friend can"t fix the tables, climate you need to change your query:

UPDATE T1SET ORDINAL = DATEDIFF(DAY, case WHEN ISDATE(T2.Opening_Date) = 1 then T2.OpeningDate END, situation WHEN ISDATE(T1.

See more: Facebook Group Hack : How To Hack A Facebook Group ? How To Growth Hack/Grow A Facebook Group

) = 1 then T1.Date END)FROM dbo.FactTransactions together T1INNER sign up with dbo.DimStore as T2 on T1.cod_store = T2.cod_storeKeyWHERE ISDATE(T2.Opening_Date) = 1 and ISDATE(T1.) = 1;And