Geek asks and answers

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

I need to compare the dates in 2 tables, but the problem is that one table has a date in DD-Mon-YY format and the other in YYYYMM format.

I need to make both of them YYYYMM for 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 I need to adapt this query in?

+3
source share
2 answers

Since it offer_dateis a number and has lower accuracy than your real dates, this may work ...
- Convert the real date to a format string YYYYMM
- Convert this value to INT
- Compare the result with yoursoffer_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

, create_date, .

, offer_date, - SCAN SEEKs.

+2

- ? offer_date :

SELECT *
FROM offers
WHERE to_char(offer_date, 'YYYYMM') = (SELECT to_date(create_date, 'YYYYMM') FROM customers where id = '12345678') AND
      offer_rate > 0
+1

More articles:


All Articles