2

I would like to run the query to show the data before 30 days and after 30 days, I know i should use CURRENT TIMESTAMP to do this and I'm able to run data before 30 days but not after 30 days. Below is my query and kindly advise this situation. Thanks!

COB.COB_FA  > CURRENT TIMESTAMP - 30 days and COB.COB_FA  > CURRENT TIMESTAMP + 30 days
CK Ang
  • 111
  • 11
  • it's related to your system's `undo_retention` parameter. – Barbaros Özhan Oct 11 '18 at 10:20
  • `CURRENT_TIMESTAMP`, no? What do you mean you can't? Do you get an error? Are there records that appear that you don't expect? https://stackoverflow.com/questions/17922106/different-current-timestamp-and-sysdate-in-oracle – kchason Oct 11 '18 at 10:21
  • @kchason Hi, i will received empty result, it should not be empty result – CK Ang Oct 11 '18 at 10:23
  • If what you have is truly your WHERE condition, the first part is useless, as for when the second part is true (`COB.COB_FA > CURRENT TIMESTAMP + 30 days`) the first will inherently always be. Unless that inequality is backwards? – kchason Oct 11 '18 at 10:25

4 Answers4

0

Try below with current_timestamp (+/-) interval '30' day

COB.COB_FA  < current_timestamp - interval '30' day or COB.COB_FA  > current_timestamp + interval '30' day
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • That has the same issue that the initial question has. x > -30 will always be true when x > 30 is true – kchason Oct 11 '18 at 10:49
  • @kchason, I thought the only problem is date addition , so I didn't notice it, however thank you for pointing out – Fahmi Oct 11 '18 at 10:51
0

I think you simply want or:

COB.COB_FA < CURRENT TIMESTAMP - interval '30' day or
COB.COB_FA  > CURRENT TIMESTAMP + '30' day
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

sysdate can do this.

`COB.COB_FA between sysdate - 30 and sysdate + 30

Kutlu Ozel
  • 182
  • 1
  • 7
0

this will work:

create table table_date(dd date);
insert into table_date values(trunc(sysdate));

insert into table_date values(trunc(sysdate)+1);

insert into table_date values(trunc(sysdate)+2);

insert into table_date values(trunc(sysdate)+9);

insert into table_date values(add_months(trunc(sysdate),-5));

//inserting more of them

alter session set nls_date_format = 'dd/MON/yyyy hh24:mi:ss';
SELECT * FROM table_date order by dd;

11/MAY/2018 00:00:00
11/JUL/2018 00:00:00 
11/OCT/2018 00:00:00
12/OCT/2018 00:00:00
13/OCT/2018 00:00:00
18/OCT/2018 00:00:00
20/OCT/2018 00:00:00
11/JAN/2019 00:00:00
11/MAR/2019 00:00:00

SELECT * FROM table_date where dd>add_months(current_timestamp,1) or 
dd<add_months(current_timestamp,-1);

sample output:

11/JAN/2019 00:00:00
11/MAR/2019 00:00:00
11/JUL/2018 00:00:00
11/MAY/2018 00:00:00
Nikhil S
  • 3,786
  • 4
  • 18
  • 32