SELECT 1 + TRUNC (birthday_dt) - TRUNC (birthday_dt, 'IW') day_of_week
, birthday_dt
+ CASE 1 + TRUNC (birthday_dt) - TRUNC (birthday_dt, 'IW')
WHEN 6 THEN 2
WHEN 7 THEN 1
ELSE 0
END corrected_dt
, to_char(
birthday_dt
+ CASE 1 + TRUNC (birthday_dt) - TRUNC (birthday_dt, 'IW')
WHEN 6 THEN 2
WHEN 7 THEN 1
ELSE 0
END
, 'fmDay') corrected_day
from
(
select to_date('19/10/2015','DD/MM/YYYY') birthday_dt from dual --monday
union all
select to_date('20/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('21/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('22/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('23/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('24/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('25/10/2015','DD/MM/YYYY') birthday_dt from dual --sunday
)