How to select records from January 1, 2021 to last week

I was wondering how to make a query where I can select data from Jan 1, 21 to last week. I tried to put it in the date filter like this:

where start_date between TO_DATE('2021-01-01', 'yyyy-mm-dd') 
                     and TO_DATE('2021-06-13', 'yyyy-mm-dd')

but I would have to manually go in every week to change the end date, I was wondering if there is a more efficient way to select for data up until last week (meaning data until Sunday) from today’s week, so that even if I run the query two days from today, I only get data from Jan 1 to this past Sunday?

Answer

You can TRUNCate SYSDATE to the start of the ISO-week (which will always be a Monday) and then find the values which are less than that:

SELECT *
FROM   table_name
WHERE  start_date >= DATE '2021-01-01'
AND    start_date <  TRUNC( SYSDATE, 'IW' )

This has the advantage that it is independent of language and location so the query will behave exactly the same regardless of any settings the database or the user’s session may have.

It will also get all the values that may occur on Sunday (from 00:00:00 to 23:59:59).

If you want the value from the start of the current year (rather than from the fixed lower bound of 2021-01-01) then you can use:

SELECT *
FROM   table_name
WHERE  start_date >= TRUNC( SYSDATE, 'YYYY' )
AND    start_date <  TRUNC( SYSDATE, 'IW' )

db<>fiddle here