Get first and last calendar day of the year

user_4106268 0
Viewed 227

I'm looking for a way to get the first and the last calendar day in french locale for a given year on Oracle.

For example, the first calendar day of 2020 is 30/12/2019 (because its the monday of week 1 of 2020) and the last calendar day is 03/01/2021 (because its the sunday of week 53 of 2020).

I got plenty of solutions for 01/01/2020 and 31/12/2020 but not for the calendar first and last day.

I tried with to_date with select trunc(to_date('1 2020','IW YYYY'), 'YEAR') from dual; but got ORA-01820 error code. with select to_date('1 2020','DDD YYYY') from dual; i got 01/01/20 00:00:00

Thanks you for help.

date oracle sql
1 Answers

You could do:

select 
    trunc(date'2020-01-01', 'IW') first_calendar_day_2020, 
    trunc(date'2020-12-31', 'IW') + 6 last_calendar_day_2020
from dual

IW gives you the first day of the ISO week for the corresponding date. If you want the sunday of that week, you can just add 6 days.

Demo on DB Fiddle:

FIRST_CALENDAR_DAY_2020 | LAST_CALENDAR_DAY_2020
:---------------------- | :---------------------
30-DEC-19               | 03-JAN-21
user_106767160
Related