I have a to compare dates in 2 tables but the problem is that one table has the date in DD-Mon-YY format and the other in YYYYMM format.
I need to make both of them YYYYMM for the comparison.
I need to create something like this:
SELECT * FROM offers
WHERE offer_date = (SELECT to_date(create_date, 'YYYYMM') FROM customers where id = '12345678')
AND offer_rate > 0
where create_date is something like 12-Mar-2006 and offer_date is something like 200605
Any ideas where I need to adapt this query??
This question is related to
sql
oracle
date-format
to-date
As offer_date is an number, and is of lower accuracy than your real dates, this may work...
- Convert your real date to a string of format YYYYMM
- Conver that value to an INT
- Compare the result you your offer_date
SELECT
*
FROM
offers
WHERE
offer_date = (SELECT CAST(to_char(create_date, 'YYYYMM') AS INT) FROM customers where id = '12345678')
AND offer_rate > 0
Also, by doing all the manipulation on the create_date you only do the processing on one value.
Additionally, had you manipulated the offer_date you would not be able to utilise any index on that field, and so force SCANs instead of SEEKs.
Am I missing something? You can just convert offer_date in the comparison:
SELECT *
FROM offers
WHERE to_char(offer_date, 'YYYYMM') = (SELECT to_date(create_date, 'YYYYMM') FROM customers where id = '12345678') AND
offer_rate > 0
Source: Stackoverflow.com