CODEWITHSUNDEEP
sqloracle-databasedate
Boom
Boom

Reputation: 29

Retrieve data from friday 7pm to current date in Oracle SQL

Retrive data from previous friday 7PM to current date.

Example: today date 17 April 6.59PM will retrive data from 10 April 7PM until 17April 6.59PM.

I just have idea something like this:-

case
  when to_char(sysdate, 'Day') = 'Saturday' then
   select ODAT_LOC where ODAT_LOC BETWEEN sysdate and sysdate -1
  when to_char(sysdate, 'Day') = 'Sunday' then
   select ODAT_LOC where ODAT_LOC BETWEEN sysdate and sysdate -2
  when to_char(sysdate, 'Day') = 'Monday' then
   select ODAT_LOC where ODAT_LOC BETWEEN sysdate and sysdate -3
  when to_char(sysdate, 'Day') = 'Tuesday' then
   select ODAT_LOC where ODAT_LOC BETWEEN sysdate and sysdate -4 
  when to_char(sysdate, 'Day') = 'Wednesday' then
   select ODAT_LOC where ODAT_LOC BETWEEN sysdate and sysdate -5 
  when to_char(sysdate, 'Day') = 'Thursday' then
   select ODAT_LOC where ODAT_LOC BETWEEN sysdate and sysdate -6 
  when to_char(sysdate, 'Day') = 'Friday' then
   select ODAT_LOC where ODAT_LOC BETWEEN sysdate and sysdate -7 
  when to_char(sysdate, 'Day') = 'Friday' then
   select ODAT_LOC where ODAT_LOC BETWEEN sysdate and sysdate  
  else
    ''
end ODAT_LOC

But unable to control on time.

Anyone have idea how to make it in oracle sql.

Thanks.

Upvotes: 1

Views: 120

Answers (1)

Slkrasnodar
Slkrasnodar

Reputation: 824

This will get you all rows from your table from the previous Friday 7pm. You don't need the CASE.

select * FROM YOURTABLE 
where ODAT_LOC >= (next_day(trunc(sysdate), 'FRI') - 7 + interval '19' hour)

I am assuming ODAT_LOC is of DATE type.

SQL> 
select sysdate, next_day(trunc(sysdate), 'FRI') from dual;

SYSDATE              NEXT_DAY(TRUNC(SYSDA
-------------------- --------------------
2020-04-16-THU 12:22 2020-04-17-FRI 00:00


SQL> 
select sysdate, next_day(trunc(sysdate), 'FRI') - 7 from dual;

SYSDATE              NEXT_DAY(TRUNC(SYSDA
-------------------- --------------------
2020-04-16-THU 12:22 2020-04-10-FRI 00:00


SQL> 
select sysdate, next_day(trunc(sysdate), 'FRI') - 7  + interval '19' hour from dual;

SYSDATE              NEXT_DAY(TRUNC(SYSDA
-------------------- --------------------
2020-04-16-THU 12:22 2020-04-10-FRI 19:00

Upvotes: 3

Related Questions