5

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

Tom
  • 12,776
  • 48
  • 145
  • 240
  • 1
    Why are you having a problem? Is offer_date a `VARCHAR2` (string)? – Phil May 10 '12 at 14:53
  • Dates don't have formats like that. If you want that format, you want a string by definition. So, are you looking for strings (with that format) or DATETIMEs (which doesn't have a format). And so, if your fields "have different formats", what are their actual data types? DATETIMEs, a VHARCHAR()s, etc? – MatBailie May 10 '12 at 14:55
  • @phil - offer_date is a NUMBER field – Tom May 10 '12 at 14:57
  • @Dems - in addition to the above comment, create_date is a DATE field – Tom May 10 '12 at 15:05
  • @Tom: It would better if you could convert the datatypes of those date columns to `DATE`. Why use `NUMBER` for a date column? – ypercubeᵀᴹ May 10 '12 at 15:11
  • @ypercude - unfortunately I'm not in a position to make that kind of a change - I just have to muddle through with what I am given! – Tom May 10 '12 at 15:13

2 Answers2

5

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.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I think you're somewhere near the solution. Would it help I was to say that create_date is DATE format and offer_date is NUMBER format (which really isn't all that helpful!) – Tom May 10 '12 at 15:02
  • `WHERE to_char(offer_date, 'YYYYMM') = (SELECT to_char(create_date, 'YYYYMM') FR...` did the tricks... thanks for pointing me in the right direction. – Tom May 10 '12 at 15:11
  • 2
    @Tom : Do note; wrapping `offer_date` in `to_char()` will obfuscate any index on that field. Instead, you can do all the processing on the `create_date`, as you only need to do that once for this query, then make use of indexes for an index seek on `offer_date`. *[Doing manipulation on scalar constants in nearly infinitely preferable to manipulating the data being searched.]* – MatBailie May 10 '12 at 15:20