Useful SQL macro for dates

05/09/24 13:23

Here some utilities to simplify the handling of dates…

—- Get the day of the week (1-Monday to 7-Sunday) in a locale-independent way


CREATE OR REPLACE FUNCTION get_dow(p_date IN DATE)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
RETURN q'~(p_date - TRUNC(p_date, 'IW') + 1)~' ;
END;
/

-- get the first day of week (1-7 : Mon-Sun) of the Month

CREATE OR REPLACE FUNCTION get_month_first_dow(p_dat IN DATE, p_dow IN NUMBER)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
RETURN q'~trunc(p_dat, 'MM') + mod(8 - (trunc(p_dat, 'MM') - TRUNC(trunc(p_dat, 'MM'), 'IW') + 1) + (greatest(1, least(p_dow, 7))-1), 7) ~' ;
END get_month_first_dow;
/

-- get the last day of week (1-7 : Mon-Sun) of the Month


CREATE OR REPLACE FUNCTION get_month_last_dow(p_dat IN DATE, p_dow IN NUMBER)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
RETURN q'~last_day(p_dat) - mod(last_day(p_dat) - TRUNC(last_day(p_dat), 'IW') + 8 - greatest(1, least(p_dow, 7)), 7) ~' ;
END get_month_last_dow;
/

-- first day of week of a year


CREATE OR REPLACE FUNCTION get_year_first_dow(p_year IN NUMBER, p_dow IN NUMBER)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
RETURN q'~get_month_first_dow( trunc(to_date(p_year,'YYYY'),'YEAR'), greatest(1, least(p_dow, 7)) )~' ;
END get_year_first_dow;
/

-- last day of week of a year

CREATE OR REPLACE FUNCTION get_year_last_dow(p_year IN NUMBER, p_dow IN NUMBER)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
RETURN q'~get_month_last_dow( trunc(to_date(p_year+1,'YYYY'),'YEAR')-1, greatest(1, least(p_dow, 7)) )~' ;
END get_year_last_dow;
/

-- get the nth day of week from a specific month
-- may return date from another month
-- nth = 0 will return the previous


CREATE OR REPLACE FUNCTION get_month_nth_dow(p_dat IN DATE, p_dow IN NUMBER, p_nth IN NUMBER)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
RETURN q'~trunc(p_dat, 'MM') + (mod(8 - (trunc(p_dat, 'MM') - TRUNC(trunc(p_dat, 'MM'), 'IW') + 1) + (greatest(1, least(p_dow, 7))-1), 7) + (p_nth-1)*7) ~' ;
END get_month_nth_dow;
/

-- get the nth day of week from a specific date


CREATE OR REPLACE FUNCTION get_date_nth_dow(p_dat IN DATE, p_dow IN NUMBER, p_nth IN NUMBER)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
RETURN q'~(p_dat + 7 + greatest(1, least(p_dow, 7)) - get_dow(p_dat) + (p_nth-1)*7)~' ;
END get_date_nth_dow;
/

CREATE OR REPLACE TYPE t_date_tab AS TABLE OF DATE;
/

-- returns all the dates of the year that are the p_nth p_dow in each month
-- e.g. all the 2nd Monday of each month in year



CREATE OR REPLACE FUNCTION get_year_month_nth_dow(p_year IN NUMBER, p_dow IN NUMBER, p_nth IN NUMBER)
RETURN t_date_tab
PIPELINED
IS
BEGIN
FOR rec IN (
with months(dat) as (
select add_months(trunc(to_date(p_year, 'YYYY'), 'YEAR'), level-1)
connect by level <= 12
)
select get_month_nth_dow(dat, p_dow, p_nth) as dat
from months
)
LOOP
PIPE ROW(rec.dat);
END LOOP;
RETURN ;
END get_year_month_nth_dow;
/

-- get next dow (strictly) after a date


CREATE OR REPLACE FUNCTION get_next_dow(p_dat IN DATE, p_dow IN NUMBER)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
RETURN q'~(p_dat + mod(7 + greatest(1, least(p_dow, 7)) - get_dow(p_dat), 7) + 7*(get_dow(p_dat)=greatest(1, least(p_dow, 7))))~' ;
END get_next_dow;/

-- get prev dow (strictly) before a date


CREATE OR REPLACE FUNCTION get_prev_dow(p_dat IN DATE, p_dow IN NUMBER)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
RETURN q'~(p_dat - 7 + mod(7 + greatest(1, least(p_dow, 7)) - get_dow(p_dat), 7))~' ;
END get_prev_dow;
/