August 15, 2017 at 2:20 pm
Hello, I'm able to convert a yyyy-mm-dd formated DATE column into an Integer, but when I reference that column in the join I still get: Operand type clash: date is incompatible with int.
The Int is in the Dim_Date table, the key column, and the DATE is in my main table, where I converted that column to Int. How can I get around this?
This is how I converted that column: cast(convert(char(8), [actvt_dt], 112) as int) as "Date"
Thanks
August 15, 2017 at 2:30 pm
So if I understand, you've only converted the column in Dim_Date table, not the table actvt_dt is in? If you want to join on those two columns, they should be the same data type. Converting in the join will cause it not to be able to use an index on actvt_dt if there is one.
August 15, 2017 at 2:39 pm
Chris Harshman - Tuesday, August 15, 2017 2:30 PMSo if I understand, you've only converted the column in Dim_Date table, not the table actvt_dt is in? If you want to join on those two columns, they should be the same data type. Converting in the join will cause it not to be able to use an index on actvt_dt if there is one.
The Dim_Date dates_key is already a Int, my main table has a actvt_dt column that I'm joining on, and it's a DATE format, and I converted it to an Integer, however when I join, between the Dim_Date DateKey and the maintable converted column of actvt_dt, I get: Operand type clash: date is incompatible with int.
August 15, 2017 at 2:56 pm
quinn.jay - Tuesday, August 15, 2017 2:39 PMChris Harshman - Tuesday, August 15, 2017 2:30 PMSo if I understand, you've only converted the column in Dim_Date table, not the table actvt_dt is in? If you want to join on those two columns, they should be the same data type. Converting in the join will cause it not to be able to use an index on actvt_dt if there is one.The Dim_Date dates_key is already a Int, my main table has a actvt_dt column that I'm joining on, and it's a DATE format, and I converted it to an Integer, however when I join, between the Dim_Date DateKey and the maintable converted column of actvt_dt, I get: Operand type clash: date is incompatible with int.
I should mention I'm doing this in creating a view
August 15, 2017 at 4:55 pm
quinn.jay - Tuesday, August 15, 2017 2:39 PMChris Harshman - Tuesday, August 15, 2017 2:30 PMSo if I understand, you've only converted the column in Dim_Date table, not the table actvt_dt is in? If you want to join on those two columns, they should be the same data type. Converting in the join will cause it not to be able to use an index on actvt_dt if there is one.The Dim_Date dates_key is already a Int, my main table has a actvt_dt column that I'm joining on, and it's a DATE format, and I converted it to an Integer, however when I join, between the Dim_Date DateKey and the maintable converted column of actvt_dt, I get: Operand type clash: date is incompatible with int.
what's the actual column datatype in the table, did you change the actvt_dt datatype to INT or is it DATE and you're doing the conversion in your View? I guess it's still not clear to me.
August 16, 2017 at 4:36 am
quinn.jay - Tuesday, August 15, 2017 2:20 PMHello, I'm able to convert a yyyy-mm-dd formated DATE column into an Integer, but when I reference that column in the join I still get: Operand type clash: date is incompatible with int.
The Int is in the Dim_Date table, the key column, and the DATE is in my main table, where I converted that column to Int. How can I get around this?This is how I converted that column: cast(convert(char(8), [actvt_dt], 112) as int) as "Date"
Thanks
Can you post the query which is throwing the error?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply