Convert YYYY-MM-DD into Interger in Select, and still recognize INT date in the Join

  • 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

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

  • Chris Harshman - Tuesday, August 15, 2017 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.

    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.

  • quinn.jay - Tuesday, August 15, 2017 2:39 PM

    Chris Harshman - Tuesday, August 15, 2017 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.

    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

  • quinn.jay - Tuesday, August 15, 2017 2:39 PM

    Chris Harshman - Tuesday, August 15, 2017 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.

    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.

  • quinn.jay - Tuesday, August 15, 2017 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

    Can you post the query which is throwing the error?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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