Oracle SQL get last Sunday [closed]

I am currently using the below statement to sucessfully get it

SELECT
    next_day(sysdate-7, 'Sunday')
FROM
    dual;

Is there any more elegant way to get it? If I try to use last_day, it gives an error.

Answer

If you feel not comfortable with NLS session parameters, which is required for next_day function, you can use trunc function: truncate your date to ISO week, that starts from Monday, and subtract one day to get to Sunday.

But keep in mind, that Oracle’s date is essentially a date and time, and trunc function sets all the lower granularity elements of date (lower than truncation unit) to their initial values, so you’ll get to the midnight of the previous Sunday. In contrast, next_day preserves the time portion, so two dates retrieved with next_day and trunc will not be equal in full.

If you need to keep the same result, but without NLS dependency, you can do some interval calculations, that will preserve the time portion.

More about date and time format elements can be found in the documentation.

Below is all the above solutions compared:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'

select
  --IW here stands for ISO week
  trunc(sysdate, 'IW') - 1 as dt_trunc,
  next_day(sysdate - 7, 'Sunday') as dt_next_day,
  --double quoted part in date format is needed to embed string constants in the format
  sysdate - to_dsinterval(to_char(sysdate, 'D "00:00:00"')) + 1 as dt_interval
from dual

DT_TRUNC            | DT_NEXT_DAY         | DT_INTERVAL        
:------------------ | :------------------ | :------------------
2021-04-18 00:00:00 | 2021-04-18 18:02:42 | 2021-04-18 18:02:42

db<>fiddle here