Oracle previous_day function

Oracle has a SQL function NEXT_DAY (date, char) which returns the date of the first weekday named by char that is later than the date. But Oracle does not have a PREVIOUS_DAY, for instance to get the Monday of the current week.

Now to get the Monday of the week a certain date is in, you can easily use TRUNC(date,’iw’). And you think perhaps that TRUNC(date,’day’) does the same? Well, that depends on your NLS_TERRITORY setting, because e.g. in America the first day of the week is Sunday, but in The Netherlands it is Monday!

So to get last Monday’s date is not such a great deal. How about Tuesday? Or any other day of the week?

I created next function PREVIOUS_DAY, which is the opposite of Oracle’s NEXT_DAY. It has the same parameters and is independent of the NLS_TERRITORY.

-- The counterpart of the standard Oracle NEXT_DAY function.
create or replace function previous_day
    (pi_date in date := sysdate
    ,pi_day  in varchar2 := to_char(sysdate,'dy','nls_date_language = english')
return date
    l_date    date;
    select (pi_date - level) as previous_day
    into l_date
    from dual
    where to_char((trunc(pi_date) - level),'dy','nls_date_language = english') = lower(substr(pi_day,1,3))
    connect by level <= 7;
    return l_date;
end previous_day;


Comments are closed.

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy