PL/SQL Problem: Not a Valid Month Error when Selecting via Business Object report

Question

This probably isn't as complicated as it should be, but Business Objects seems to be very, very strict in how types are used versus SQL Developer. Here's the piece of the statement in question:

ship_date between '01' || '-' || w_current_ora_month || '-' || to_char(to_date(w_last_day, 'DD-MON-RR HH:MI:SS AM'), 'yy') and to_char(to_date(w_last_day, 'DD-MON-RR HH:MI:SS AM'))

w_current_ora_month VARCHAR2(3) is filled via:

SELECT to_char(sysdate,   'MON')
  INTO w_current_ora_month
  FROM dual;

w.last_day DATE is filled via:

SELECT trunc(LAST_DAY('01' || '-' || w_current_ora_month || '-' || to_char(w_year)))
    into w_last_day
    from dual

Why am I getting a Not Valid Month error when pulling from Business Objects? I've already found the Business objects is way strict on types, so I'm wondering if I'm doing something wrong here. All of this works fine in SQL developer, but I've had to tweak this statement over and over again to try to get it to work when Business Objects uses it.

Answer 1

You're relying on implicit conversion from strings to dates, which is always a bad idea.

If you have to convert to a string then back to a date, always use to_date and a date mask. Otherwise you're depending on the NLS variables, which can be changed within a session (almost certainly the cause of your problem).

However, in this case, you don't have to. Your condition can be simplified to:

ship_date between trunc(sysdate,'MON') and last_day(trunc(sysdate))

As @APC pointed out, if your field contains a time component, you'll want to get everything through the end of the last day of the month. This can be achieved several ways:

ship_date between trunc(sysdate,'MON') 
              and last_day(trunc(sysdate))+(86399/86400)

ship_date between trunc(sysdate,'MON') 
              and add_months(trunc(sysdate,'MON'),1)-(1/86400)

ship_date >= trunc(sysdate,'MON') 
    and ship_date < add_months(trunc(sysdate,'MON'),1)

I tend to prefer the last version as it'll continue to work if you decide to change the field to a TIMESTAMP down the road.