CODEWITHSUNDEEP
sqldb2
user7398700
user7398700

Reputation: 3

DB2 select with different date format

My problem is that in an ancient database there are two tables that I need to query for matching rows based on date. Only in one table date is represented as YYYYMM as a decimal(6) and in the other as YYYY-MM-DD as a date.

How do I join these two tables together?

I am perfectly happy searching on any day or day 01.

Upvotes: 0

Views: 132

Answers (2)

jmarkmurphy
jmarkmurphy

Reputation: 11473

You can format that date as YYYYMM using TO_CHAR or VARCHAR_FORMAT, then join the two tables together.

Assuming table A has the date field in col1, and table B has the decimal(6) field in col2, it would look like this:

select *
  from A
    join B on dec(varchar_format(a.col1, 'YYYYMM'),6,0) = b.col2

Upvotes: 1

bp89
bp89

Reputation: 91

You can perform join on those two tables. Suppose first table where date is stored as decimal(6) is A in column col1 and another table be B with date stored as column col2.The query would be something like below :

    SELECT * FROM A, B
    WHERE INT(A.col1) = INT(SUBSTR(B.col2,1,4)|| SUBSTR(B.col2,6,2))

Upvotes: 0

Related Questions