CODEWITHSUNDEEP
sqloracle-databasedate
comp_user
comp_user

Reputation: 60

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?

Upvotes: 0

Views: 2693

Answers (3)

MT0
MT0

Reputation: 167981

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

The way I understood it, you're looking for a row generator. If that's so, here you are:

First, a few settings (as my database speaks Croatian); you wouldn't do that:

SQL> alter session set nls_date_format = 'dd.mm.yyyy, day';

Session altered.

SQL> alter session set nls_date_language = 'english';

Session altered.

Query itself, and the result:

SQL> select trunc(sysdate, 'yyyy') + level - 1 as datum
  2  from dual
  3  connect by level <= next_day(trunc(sysdate) - 7, 'sunday') - trunc(sysdate, 'yyyy') + 1
  4  order by datum;

DATUM
---------------------
01.01.2021, friday
02.01.2021, saturday
03.01.2021, sunday
04.01.2021, monday
05.01.2021, tuesday
06.01.2021, wednesday
07.01.2021, thursday
08.01.2021, friday
<snip>
09.06.2021, wednesday
10.06.2021, thursday
11.06.2021, friday
12.06.2021, saturday
13.06.2021, sunday

164 rows selected.

SQL>

As you said:

so that even if I run the query two days from today, I only get data from Jan 1 to this past Sunday?

let's pretend it is two days from today, i.e. 16.06.2021 (Wednesday). I'm now connected as a privileged user so that I could change system's date (you wouldn't be doing that):

SQL> alter system set fixed_date = '16.06.2021';

System altered.

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> alter session set nls_date_format = 'dd.mm.yyyy, day';

Session altered.

SQL> select sysdate from dual;

SYSDATE
---------------------
16.06.2021, wednesday

See? Wednesday.

Let's try the same query again:

SQL> select trunc(sysdate, 'yyyy') + level - 1 as datum
  2  from dual
  3  connect by level <= next_day(trunc(sysdate) - 7, 'sunday') - trunc(sysdate, 'yyyy') + 1
  4  order by datum;

DATUM
---------------------
01.01.2021, friday
02.01.2021, saturday
03.01.2021, sunday
04.01.2021, monday
<snip>
11.06.2021, friday
12.06.2021, saturday
13.06.2021, sunday

164 rows selected.

SQL>

Looks OK to me.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can use below where clause -

WHERE start_date BETWEEN TO_DATE('2021-01-01', 'yyyy-mm-dd') AND (TRUNC(SYSDATE) - TO_CHAR(SYSDATE, 'd'));

Upvotes: -1

Related Questions