SSRS 2005 passing datetime parameter to Oracle

Question

I have a SSRS 2005 report runs on a Oracle 9 database.

The report use this simple query to return a dataset

    SELECT order_number FROM apps.oe_order_headers_all  
    WHERE ordered_date >= to_date(:start_date,'DD-MON-YYYY') 
    AND ordered_date  < to_date(:end_date,'DD-MON-YYYY') +1

The parameters work fine if they are of type string in SSRS. E.g. 01-JAN-2014 for both start_date and end_date. But if changed to datetime the report returns nothing (if in string, the report returns many rows). I really want to use the date picker control of SSRS.

This is probably more of a SSRS thing than Oracle thing?

Alex Poole Alex Poole · Accepted Answer · 2014-02-28T17:10:32

This is probably a date conversion issue, but I'm surprised it isn't either erroring, or returning the same data - at least for the example date you gave.

In your previous question you were entering a string, so adding the to_date() made sense. But if you're binding it as a date then you do not want those.

This is fine:

to_date(<string>,'DD-MON-YYYY')

But this:

to_date(<date>,'DD-MON-YYYY')

is really doing:

to_date(to_char(<date>,<NLS_DATE_FORMAT>), 'DD-MON-YYYY')

If your `NLS_DATE_FORMAT happens to be the same as the fixed format you supplied, then this is kind of OK as it's just a redundant conversion to a string and back. But if they are different then you'll get errors or incorrect results.

For example, if my NLS_DATE_FORMAT is MM/DD/YYYY' thento_date(sysdate, 'DD-MON-YYYY) gets anORA-01843: not a valid montherror, because it's trying to interpret28as the month and02` as the day.

So you just need to simplify it to:

SELECT order_number FROM apps.oe_order_headers_all  
WHERE ordered_date >= :start_date
AND ordered_date  < :end_date +1

Given the odd error you get from this, you could also try:

AND ordered_date < :end_date + interval '1' day

... but without understanding why the simpler +1 is erroring I'm not confident that will help. It doesn't seem to be following the normal datetime arithmetic rules. This is another possibility but should not be necessary:

AND ordered_date < CAST(:end_date AS DATE) + 1

SSRS 2005 passing datetime parameter to Oracle

1 Answers