Oracle SQL convert date format from DD-Mon-YY to YYYYMM

date-format, oracle, sql, to-date

Solution

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.

Problem

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??

Original source