|
View previous topic :: View next topic
|
| Author |
Message |
Shobs
New User
Joined: 21 Jan 2010
Posts: 6
Location: Pune
|
|
|
|
| I need to write a join query on two tables where in I need to join the tables on two dates, however the format of the dates is different. In one table the format of the date is in DATE format e.g 2008-05-06 and the other table has date in format as '06MAY2008' (CHARACHTER (9)).How do I do the comparison? Thanks in Advance. |
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| You better go with a subquery rather than a join. This join will never be effective due to different data formats. |
|
| Back to top |
|
|
Shobs
New User
Joined: 21 Jan 2010
Posts: 6
Location: Pune
|
|
|
|
| Can you please help me with the sub-query as I am new to DB2? Thanks. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Code: |
SELECT * FROM TABLE A WHERE DATE_COL IN
(SELECT SUBSTR('06MAY2008',6,4)||'-'||
CASE SUBSTR('06MAY2008',3,3)
WHEN 'JAN' THEN '01'
WHEN 'FEB' THEN '02'
-----
-----
WHEN 'MAY' THEN '05'
-----
------
END
||'-'||SUBSTR('06MAY2008',1,2)
FROM TABLE B
)
|
Change '06MAY2008' with your date column in table B.
IMO the DB design could have been done in a better way. |
|
| Back to top |
|
|
|
|
