date range in oracle apps report

Question

The following sql when run with these parameters,

:P_COMP_DATE_FROM = '15-NOV-2015' :P_COMP_DATE_TO = '15-NOV-2015'

compares as between '15-NOV-2015 00:00:00' and '15-NOV-2015 00:00:00'

Select Ordered_date
From xxcost_rep
Where DATE_COMPLETED BETWEEN NVL(fnd_date.canonical_to_date(:P_COMP_DATE_FROM), DATE_COMPLETED) AND NVL(fnd_date.canonical_to_date(:P_COMP_DATE_TO)), DATE_COMPLETED);

how can I compare this as start of the day and end of the day, so can display the correct result in the range.

I am trying the following to add 86399 seconds to make it the end of the day, but receiving error:

WHERE DATE_COMPLETED BETWEEN NVL(fnd_date.canonical_to_date(:P_COMP_DATE_FROM), DATE_COMPLETED) AND NVL(fnd_date.canonical_to_date(to_date(:P_COMP_DATE_TO,'DD-MON-YYYY')+interval '86399' second), DATE_COMPLETED)

{P_TO_CUSTOMER=, P_COMP_DATE_FROM=2015/11/15 00:00:00, P_COMP_DATE_TO=2015/11/15 00:00:00, P_TO_ORDER_NUMBER=, P_CUST_REGION=, P_TO_DATE=, P_JOB_STATUS=, P_FROM_DATE=, P_FROM_ORDER_NUMBER=, P_FROM_CUSTOMER=} Calling XDO Data Engine... --SQLException java.sql.SQLDataException: ORA-01861: literal does not match format string

Answer 1

ORA-01861: literal does not match format string

The above error is because the date literal doesn't match with the format mask.

For example,

SQL> SELECT TO_DATE('2015118','yyyy/mm/dd') FROM dual;
SELECT TO_DATE('2015118','yyyy/mm/dd') FROM dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string

You might be storing dates as string, and there might be strings with different date formats. Therefore, your function fnd_date.canonical_to_date might be failing for such date literals while converting into DATE using TO_DATE.

Also, you should not depend on your client's NLS date format. Remember, TO_DATE is NLS dependent. You should explicitly mention the format mask.

For example,

SQL> SELECT  to_date('11/18/2015 00:00:00', 'mm/dd/yyyy hh24:mi:ss') date_from,
  2          to_date('11/18/2015 23:59:59', 'mm/dd/yyyy hh24:mi:ss') date_to
  3  FROM dual;

DATE_FROM           DATE_TO
------------------- -------------------
11/18/2015 00:00:00 11/18/2015 23:59:59

In your case, you need to compare the dates. You could do it like the below example,

SQL> WITH DATA AS(
  2  SELECT DATE '2015-11-18' dt FROM dual
  3  )
  4  SELECT * FROM DATA
  5  WHERE dt
  6  BETWEEN to_date(
  7                  to_char(dt, 'mm/dd/yyyy')||' 00:00:00',
  8                  'mm/dd/yyyy hh24:mi:ss'
  9                 )
 10  AND     to_date(
 11                  to_char(dt, 'mm/dd/yyyy')||' 23:59:59',
 12                  'mm/dd/yyyy hh24:mi:ss'
 13                 );

DT
-------------------
11/18/2015 00:00:00

---

UPDATE

For the first part where you just need the start time, you don't have to add the time portion as 00:00:00since DATE has both date and time elements. When you do not mention the time portion, it defaults to midnight i.e. 00:00:00.

For example, add INTERVAL '86399' SECOND:

SQL> SELECT DATE '2015-11-18' from_date,
  2         DATE '2015-11-18' + INTERVAL '86399' SECOND to_date
  3  FROM dual;

FROM_DATE           TO_DATE
------------------- -------------------
11/18/2015 00:00:00 11/18/2015 23:59:59