CODEWITHSUNDEEP
sqloracle-databaseoracle12c
d-_-b
d-_-b

Reputation: 4502

ORA-01861 error on NUMBER(8) column

I have the following SQL, which basically checks whether the "SYSDATE" fits in between specific dates(i.e. some date <= SYSDATE <= some date + 30 days).

TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD')) BETWEEN TO_DATE(TO_CHAR(T1.WEB_STR_DT, 'YYYYMMDD')) AND
    TO_DATE(TO_CHAR(T1.WEB_STR_DT, 'YYYYMMDD')) + 30

The problem I am facing is that I get ORA-01861 even the date is in the valid date format(yyyymmdd).

What could be the cause of this error?

Upvotes: 0

Views: 122

Answers (2)

Alex Poole
Alex Poole

Reputation: 191425

You shouldn't be converting sysdate to and from a string at all, as that is wasteful but is also doing implicit conversion - which is what is causing the ORA-018611 error. Your NLS_DATE_FORMAT is not YYYYMMDD; you explicitly convert the date to that format, then try to convert the resulting string back to a date using whatever your NLS settings currently are:

select to_date(to_char(sysdate, 'YYYYMMDD')) from dual;

ORA-01861: literal does not match format string

select to_date(to_char(sysdate, 'YYYYMMDD'), 'YYYYMMDD') from dual;

TO_DATE(T
---------
13-APR-17

But instead of that you can just use trunc(sysdate). That strips the time back to midnight, so it would match if WEB_STR_DT represented today.

That isn't the only problem though. You shouldn't be storing dates as numbers in the first place, but if you must convert a number like 20170413 to a date then you need to convert it to a string first and then convert that string to a date.

Essentially you have your parentheses in the wrong place, and instead of 

TO_DATE(TO_CHAR(T1.WEB_STR_DT, 'YYYYMMDD'))

you should have:

TO_DATE(TO_CHAR(T1.WEB_STR_DT), 'YYYYMMDD')

Your code is doing the equivalent of:

select to_date(to_char(20170413, 'YYYYMMDD')) from dual;

ORA-01481: invalid number format model

because you're attempting to use date format model elements to convert a number to a string. With that parenthesis moved you do end up with the equivalent date.

So, you can do:

TRUNC(SYSDATE) BETWEEN TO_DATE(TO_CHAR(T1.WEB_STR_DT), 'YYYYMMDD')
  AND TO_DATE(TO_CHAR(T1.WEB_STR_DT), 'YYYYMMDD') + 30

Even the explicit TO_CHAR() calls are a bit unnecessary, since you aren't supply a format model for those, so even simpler would be:

TRUNC(SYSDATE) BETWEEN TO_DATE(T1.WEB_STR_DT, 'YYYYMMDD')
  AND TO_DATE(T1.WEB_STR_DT, 'YYYYMMDD') + 30

Upvotes: 1

JohnHC
JohnHC

Reputation: 11205

Number conversion issues (and what is to_chat?)... I assume you just want date with no time.

Try TRUNC:

WHERE trunc(sysdate) between trunc(T1.WEB_STR_DT) and trunc(T1.WEB_STR_DT)+30

Upvotes: 1

Related Questions