Date filtering from past two weeks-CodePudding

I have an SQL query where I'm trying to select only records from the past two weeks of the present day i.e. using the 'created_on' column. Any ideas how that is done? I know I can't use specific dates because present day is always changing.

select un.id
      , t.type_name as type
      , un.content_id
      , un.app_link
      , un.notification_text
      , t.id as type_id
      , un.seen_yn,un.created_on
      , to_char(un.created_on,'YYYY-MM-DD: HH24:MI') as timestamp
from app.user_notifications as un 
       left join ref.types as t on t.id = un.notification_type
where un.active_yn = true 
and   un.user_id =  1 
and   un.seen_yn = false                                       order by un.created_on desc

CodePudding user response：

You can compare the created_on column with an expression that calculates "two weeks before today":

un.created_on >= current_date - interval '2 weeks'

alternatively you can also subtract 14 days

un.created_on >= current_date - 14

CodePudding user response：

Add yet another condition, e.g.

... and un.created_on >= trunc(sysdate) - 14

In Oracle, when you subtract number from a DATE datatype value, you subtract that many days so - subtracting 14 is two weeks back.

SQL> select sysdate right_now,
  2         trunc(sysdate) today_midnight,
  3         trunc(sysdate) - 14 two_weeks_back
  4  from dual;

RIGHT_NOW           TODAY_MIDNIGHT      TWO_WEEKS_BACK
------------------- ------------------- -------------------
22.03.2022 07:12:54 22.03.2022 00:00:00 08.03.2022 00:00:00

SQL>

Alternatively, if you prefer interval (which actually says what you're doing), then

SQL> select trunc(sysdate) - interval '14' day from dual;

TRUNC(SYSDATE)-INTE
-------------------
08.03.2022 00:00:00

SQL>