Date Comparison in Oracle to find whether today is Last Business Day of Month

Question

I am using Oracle 11g.

So, I have this query, which provides me with the Last Business Day of a Month (any suggestions for a better query is always welcome)

select DECODE(to_char(last_day(to_date(sysdate)), 'D'),'7',
              to_char((last_day(sysdate) - 1), 'DD-MON-YYYY'),'1',
              to_char((last_day(sysdate) - 2), 'DD-MON-YYYY'),
              to_char(last_day(sysdate), 'DD-MON-YYYY'))
  into LAST_BD_OF_MONTH_P
  from dual;

Which as of today gives me this result

30-APR-2015

Now, when I compare this with sysdate + 9 to check whether its End of Month, its always giving me a No Match - see that I am converting both to date using to_date.

 select to_char(sysdate + 9,'DD-MON-YYYY')
        , DECODE(to_char(last_day(to_date(sysdate)), 'D'),'7',
          to_char((last_day(sysdate) - 1), 'DD-MON-YYYY'),'1',
          to_char((last_day(sysdate) - 2), 'DD-MON-YYYY'),
          to_char(last_day(sysdate), 'DD-MON-YYYY')) as EOMBD
        , case when to_date(sysdate + 9,'DD-MON-YYYY') = 
                    to_date(DECODE(to_char(last_day(to_date(sysdate)), 'D'),'7',
                    to_char((last_day(sysdate) - 1), 'DD-MON-YYYY'),'1',
                    to_char((last_day(sysdate) - 2), 'DD-MON-YYYY'),
                    to_char(last_day(sysdate), 'DD-MON-YYYY')), 'DD-MON-YYYY') 
               then 'Match' 
               else 'No Match' 
          end as Match
    from dual;

But, if I change this query to convert sysdate + 9 as char using to_char, it works and gives me a Match.

 select to_char(sysdate + 9,'DD-MON-YYYY')
        , DECODE(to_char(last_day(to_date(sysdate)), 'D'),'7',
          to_char((last_day(sysdate) - 1), 'DD-MON-YYYY'),'1',
          to_char((last_day(sysdate) - 2), 'DD-MON-YYYY'),
          to_char(last_day(sysdate), 'DD-MON-YYYY')) as EOMBD
        , case when /*convert using to_char*/to_char(sysdate + 9,'DD-MON-YYYY') = 
                    DECODE(to_char(last_day(to_date(sysdate)), 'D'),'7',
                    to_char((last_day(sysdate) - 1), 'DD-MON-YYYY'),'1',
                    to_char((last_day(sysdate) - 2), 'DD-MON-YYYY'),
                    to_char(last_day(sysdate), 'DD-MON-YYYY')) 
               then 'Match' 
               else 'No Match' 
          end as Match
    from dual;

I understand that in the second query, it matching to strings and thus giving me a Match. Is there any way, this comparison provides me a result of Match without converting this to char?

Its true that this gives me the desired output, but I would like not to use the to_char function here.

PS : LAST_BD_OF_MONTH_P is declared as DATE

INCLUDED ANSWER

 select to_char(sysdate + 9,'DD-MON-YYYY'),  DECODE(to_char(last_day(to_date(sysdate)), 'D'),'7',
      to_char((last_day(sysdate) - 1), 'DD-MON-YYYY'),'1',
      to_char((last_day(sysdate) - 2), 'DD-MON-YYYY'),
      to_char(last_day(sysdate), 'DD-MON-YYYY')) as EOMBD
    , case when trunc(sysdate + 9) = 
                to_date(DECODE(to_char(last_day(to_date(sysdate)), 'D'),'7',
                to_char((last_day(sysdate) - 1), 'DD-MON-YYYY'),'1',
                to_char((last_day(sysdate) - 2), 'DD-MON-YYYY'),
                to_char(last_day(sysdate), 'DD-MON-YYYY')), 'DD-MON-YYYY') 
           then 'Match' 
           else 'No Match' 
      end as Match
from dual;

What is your NLS_DATE_FORMAT? You're relying on that, which you shouldn't, but I'm getting various errors from both your queries at the moment - knowing your setting might let these run at least, as a starting point. Why do you want the LAST_BD_OF_MONTH_P as a string rather than a date type? — Alex Poole, Apr 21 '15 at 15:55
@AlexPoole `DD-MON-RR`, also `LAST_BD_OF_MONTH_P` is declared as `Date` — Saagar Elias Jacky, Apr 21 '15 at 15:57
Hope this would help http://lalitkumarb.wordpress.com/2015/04/15/generate-date-month-name-week-number-day-number-between-two-dates-in-oracle-sql/ — Lalit Kumar B, Apr 21 '15 at 15:57
OK, I still get ORA-01722: invalid number from your final query. — Alex Poole, Apr 21 '15 at 15:58
`to_date(sysdate)` is useless. It converts a `date` to a `varchar` just to convert that back to a `date` again. — , Apr 21 '15 at 16:57
It should be noted that result of `to_char(..., 'D')` depends on current NLS_TERRITORY settings which may vary in each session. — Wernfried Domscheit, Apr 21 '15 at 17:06

Ditto · Answer 1 · 2015-04-24T13:46:56.727

Unfortunately, your logic isn't entirely accurate. Take Mar, 2013 for instance:

  with w_date as ( select to_date('15-mar-2013','dd-mon-yyyy') d from dual )
  select DECODE(to_char(last_day(d), 'D'),'7',
                to_char((last_day(d) - 1), 'DD-MON-YYYY'),'1',
                to_char((last_day(d) - 2), 'DD-MON-YYYY'),
                to_char(last_day(d), 'DD-MON-YYYY'))
    from w_date;

  DECODE(TO_C
  -----------
  29-MAR-2013

Which happens to be Good Friday .. so "not a business Day" ... it should spit out "Mar 28, 2013"

Don't do so much to_date / to_char conversions .. it'll cause you grief.

In order to do this kind of thing, you really need a table of holidays (or a table of business days - either way) Once you have that, the solution becomes trivial:

If you have a table of holidays:

  with w_date as ( select to_date('15-mar-2013','dd-mon-yyyy') d from dual ),
       w_holidays as (
       select to_date('29-mar-2013','dd-mon-yyyy') holiday from dual
          ),
      w_sub as (
           select last_day(d) - level + 1  dd
             from w_date
             connect by level <= 10
        )
  select max(dd)
    from w_sub s
    where to_char(dd,'d') not in ( 1,7)
      and not exists ( select * from w_holidays h
                        where h.holiday = s.dd )

  /

results:

  MAX(DD)
  ---------
  28-MAR-13

  1 row selected.

If you have a table of business days:

  with w_business_days as (
       select to_date('25-mar-2013','dd-mon-yyyy') busday from dual union all
       select to_date('26-mar-2013','dd-mon-yyyy') busday from dual union all
       select to_date('27-mar-2013','dd-mon-yyyy') busday from dual union all
       select to_date('28-mar-2013','dd-mon-yyyy') busday from dual union all
       select to_date('01-apr-2013','dd-mon-yyyy') busday from dual
          )
  select max(busday)
    from w_business_days
   where busday <= last_day(to_date('15-mar-2013','dd-mon-yyyy') )
  /

results:

  MAX(BUSDA
  ---------
  28-MAR-13

  1 row selected.

Are you sure this works? What happens when you replace the date with `30-APR-2015`? The `w_holidays` will be null and the result is `29-APR-2015` — Saagar Elias Jacky, Apr 22 '15 at 15:50
Also, I am more concerned about Bank Holidays rather than normal Holidays... :-) — Saagar Elias Jacky, Apr 22 '15 at 17:41
@Saagar: ahh, sorry, small bug: Added a "+1" in w_sub for the holiday query (it wasn't allowing for the actual last day, in Apr 2013 case, that's Apr 30), and changed "<" to "<=" in business day query for same reason. Sorry about that :) And for Bank Holidays .. no worries, just populate the Holiday table with whatever you want to act like a holiday ... Sept 11, 2001, for example ... in some cases. :( — Ditto, Apr 24 '15 at 13:48

score 1 · Accepted Answer · answered Apr 21 '15 at 16:02

This is doing an implicit conversion of sysdate + 9 to a string, using your NLS_DATE_FORMAT; and then converting back to a date:

case when to_date(sysdate + 9,'DD-MON-YYYY') =

At best this will work, if your format is also DD-MON-YYYY, but as it is you're losing the century because you're really doing:

case when to_date(to_char(sysdate + 9, 'DD-MON-RR'),'DD-MON-YYYY') =

The mismatch of RR and YYYY means you end up with year 0015, not 2015.

You can simplify that to:

case when trunc(sysdate) + 9 =

So just with that change:

 select to_char(sysdate + 9,'DD-MON-YYYY')
        , DECODE(to_char(last_day(to_date(sysdate)), 'D'),'7',
          to_char((last_day(sysdate) - 1), 'DD-MON-YYYY'),'1',
          to_char((last_day(sysdate) - 2), 'DD-MON-YYYY'),
          to_char(last_day(sysdate), 'DD-MON-YYYY')) as EOMBD
        , case when trunc(sysdate + 9) = 
                    to_date(DECODE(to_char(last_day(to_date(sysdate)), 'D'),'7',
                    to_char((last_day(sysdate) - 1), 'DD-MON-YYYY'),'1',
                    to_char((last_day(sysdate) - 2), 'DD-MON-YYYY'),
                    to_char(last_day(sysdate), 'DD-MON-YYYY')), 'DD-MON-YYYY') 
               then 'Match' 
               else 'No Match' 
          end as Match
    from dual;

TO_CHAR(SYSDATE+9,'DD-MON-YYYY') EOMBD                MATCH  
-------------------------------- -------------------- --------
30-APR-2015                      30-APR-2015          Match

I'm not sure why you're converting between dates and strings so much in other places though, you're making things complicated and letting NLS issues become an issue. You should never rely on your NLS settings, and it's easy to do so by accident with so many explicit and implicit conversions going on.

score 1 · Answer 3 · edited May 23 '17 at 12:22

My suggestion is to use the DBMS_SCHEDULER calendar. You can create a function like this:

CREATE OR REPLACE FUNCTION LAST_BD_OF_MONTH_P(the_day IN TIMESTAMP) RETURN TIMESTAMP AS
    next_run_date TIMESTAMP;
BEGIN
    DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY; BYDAY=-1 FRI', NULL, the_day, next_run_date);
    RETURN next_run_date;
END;

SELECT CAST(LAST_BD_OF_MONTH_P(DATE '2015-05-10') AS DATE) FROM dual;
=====================================
2015-05-29

See syntax for calendar here: Calendaring Syntax

Note, when you give a date after the last business day (e.g. 2015-04-30), this function will return last business day of next month (i.e. 2015-06-26).

In order to avoid this you can use

DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY; BYDAY=-1 FRI', NULL, TRUNC(the_day, 'MM'), next_run_date); instead.

In oder to cover also public holidays, have a look at one of these:

Due Date Calculated Given Start Date and Working/Lunch Hours PL/SQL

Sql: difference between two dates

Date Comparison in Oracle to find whether today is Last Business Day of Month

3 Answers3