char value and date value comparison in query

Question

one column (txn_issue_dt) is char and issue_dt is date and I am attempting to compare the two but I am self taught and I am stumped, please help.

SELECT ptf.carrid,
       ptf.cov_effdt,
       ptf.cvg_id,
       ptf.err_msg,
       ptf.error_id,
       ptf.fine_dt,
       ptf.fine_id,
       ptf.fine_status,
       ptf.polnbr,
       ptf.txn_code,
       ptf.txn_type,
       ptf.rcvd_dt
FROM   policy_tracking_fine_view ptf
       INNER JOIN canc_rein cr ON cr.polnbr = ptf.polnbr
WHERE  ptf.txn_code = '05'
AND    ptf.fine_type = 'L'
AND    ptf.fine_status_descrip NOT IN ('SUSPENDED', 'VOID')
AND    TO_CHAR (ptf.txn_issue_dt, 'yy-mon-dd') = TO_DATE (cr.issue_dt, 'mm/dd/yy');

I am getting the ORA-01481:invalid number format model and ORA-01861: literal does not match format string

Try: `AND TO_DATE(ptf.txn_issue_dt, 'yy-mon-dd') = cr.issue_dt` — Tony Andrews, Aug 25 '15 at 16:07

Boneist · Answer 1 · 2015-08-25T16:26:00.013

When you are comparing two dates, it is best if both are in DATE datatype.

If both columns are already in DATE datatype, then you don't have to worry about converting them (although you may need to worry about whether you need to TRUNC() the date to the start of the day/month/year/hour etc); you can just compare them as DATEs.

If one or more column is a string, then you are going to have to convert it into a DATE datatype, which you can do using TO_DATE.

So, in your query, it looks like you need to change

AND TO_CHAR (ptf.txn_issue_dt, 'yy-mon-dd') = TO_DATE (cr.issue_dt, 'mm/dd/yy')

to

AND TO_DATE (ptf.txn_issue_dt, 'yy-mon-dd') = cr.issue_dt

Obviously, the format of your date-as-a-string column dictates the format mask that you use. It looks like your data has 2-digit years? (Ick, ick, ick, ick! It's like Y2K never even happened...) If so, then you may be better off using a format mask of 'rr-mon-dd' in order to get a better guess as to what the first two digits of the year are.

Eg. if your string dates look like '23/08/2015' then you'd do TO_DATE(ptf.txn_issue_dt, 'dd/mm/yyyy')

NB. NEVER do a TO_DATE() on something that is already a DATE datatype; you've introduced a potential bug, as Oracle will have to do an implicit conversion behind the scenes which relies on the default nls_date_format parameter, and this could result in an error or an incorrect date being returned:

alter session set nls_date_format = 'dd/mm/yy hh24:mi:ss';

select to_char(to_date(sysdate, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss') dt from dual;

DT                 
-------------------
25/08/0015 12:25:20

score 0 · Answer 2 · answered Aug 25 '15 at 16:11

You're converting a character to a character (txt_issue_dt) and a date to a date. Always convert to the same datatype, so if you have different data types, keep one the same and convert the other. In your case:

TO_DATE(ptf.txn_issue_dt,'' ) = cr.issue_dt

So if the contents of the txn_issue_dt is something like 2015-APR-01, put ' yyyy-MON-dd' as format mask in the lefthand side of the comparison.

Keep in mind that putting functions around a column name disables index use on that table. So in my example, an index on txn_issue_dt isn't used by the sql interpreter.

char value and date value comparison in query

2 Answers2