Selecting Dates from Monday to Saturday and Hours Between Morning 8:00:00 AM To Next Day Morning 7:0-CodePudding

I am trying to work on a query where there is date selection in the where clause i.e. if sysdate is Monday I have to get the dates from Monday to Saturday and Hours Between Morning 08:00:00 AM to Next Day Morning 07:00:00 AM. I am hardcoding the dates and Hours in the where clause, When I run the query data does not show.

Query:

SELECT TO_CHAR(sysdate, 'HH24:MI:SS'), REPLACE(TO_CHAR(sysdate, 'DAY'), ' ')
  FROM dual
 WHERE TO_CHAR(sysdate, 'HH24:MI:SS') BETWEEN '08:01:00' AND '08:00:00'
   AND TO_CHAR(sysdate, 'DAY') >= 'MONDAY'
   AND TO_CHAR(sysdate, 'DAY') <= 'SATURDAY';

CodePudding user response：

You need to filter the wider range first (from 8 AM at Monday till the end of a Saturday, if I understood correctly) and then exclude time from 7 AM till 8 AM.

In the below code iw format element stands for ISO week, that starts on the Monday.

with a as (
  select
    date '2022-04-17'
        interval '01:30:00' hour to second
        interval '2' hour * level
    as dt
  from dual
  connect by level < 90
)
select
  to_char(dt, 'yyyymmdd') as day_
  , listagg(to_char(dt, 'hh24:mi'), ',')
  within group (order by dt asc) as hours
from a
where 1 = 1
  /*From Mon 08 AM*/
  and dt > trunc(dt, 'iw')  
    interval '8' hour
  /*Till Sat end of the day*/
  and dt < trunc(dt, 'iw')   6
  /*and except minutes between 7 and 8 AM*/
  and not (
    to_char(dt, 'hh24mi') < '0800'
    and to_char(dt, 'hh24mi') > '0700'
  )
group by to_char(dt, 'yyyymmdd')

DAY_     | HOURS                                                            
:------- | :----------------------------------------------------------------
20220418 | 09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30                  
20220419 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30
20220420 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30
20220421 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30
20220422 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30
20220423 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30

db<>fiddle here

CodePudding user response：

(And what if sysdate isn't Monday?)

Therefore, could you explain a little bit better what is the input (dates? One date? SYSDATE?) and what is desired output (related to that input).

Basically, I don't understand what you want. Meanwhile, errors you made (if it'll help).

Format model is wrong; this is what you did:

SQL> select to_char(sysdate, 'DAY') day, length(to_char(sysdate, 'DAY')) len from dual;

DAY              LEN
--------- ----------
FRIDAY             9

"FRIDAY" doesn't have 9 characters; it has 6 of them --> use the fm format modifier (it'll truncate trailing spaces):

SQL> select to_char(sysdate, 'fmDAY') day, length(to_char(sysdate, 'fmDAY')) len from dual;

DAY              LEN
--------- ----------
FRIDAY             6

SQL>

Today (22.04.2022) is Friday. Your query searches for data whose day is between "MONDAY" and "SATURDAY". As you're comparing strings and alphabet goes as [A, B, ..., F, G, ..., M, N, ..., S, T], "F(riday)" is NEVER between M(onday) and S(aturday) so there's zero chance that it'll work.

As of hours: which time exactly is between 08:01 and 08:00? Time doesn't go backwards (unless you meant "08:01 today and 08:00 tomorrow").

CodePudding user response：

if sysdate is Monday I have to get the dates from Monday to Saturday and Hours Between Morning 08:00:00 AM to Next Day Morning 07:00:00 AM.

You can find whether SYSDATE is Monday by comparing the day to the start of the ISO week (which will always be midnight on Monday):

SELECT *
FROM   DUAL
WHERE  SYSDATE - TRUNC(SYSDATE, 'IW') < 1

You can find out whether the hours are between 08:00 and 07:00 the next day by subtracting 8 hours and finding out whether the time is between 00:00 and 23:00:

SELECT *
FROM   DUAL
WHERE  (SYSDATE - INTERVAL '8' HOUR) - TRUNC(SYSDATE - INTERVAL '8' HOUR)) DAY TO SECOND
         <= INTERVAL '23' HOUR;

You can combine the two to find out if the day is between Monday and Saturday and the time is between 08:00 and 07:00 on the next day (so for Saturday, it would include 7 hours of Sunday) using:

SELECT *
FROM   DUAL
WHERE  (SYSDATE - INTERVAL '8' HOUR) - TRUNC(SYSDATE - INTERVAL '8' HOUR), 'IW') < 6
AND    (SYSDATE - INTERVAL '8' HOUR) - TRUNC(SYSDATE - INTERVAL '8' HOUR)) DAY TO SECOND
         <= INTERVAL '23' HOUR;

Note: This does not use TO_CHAR so it is unaffected by any changes to the NLS_TERRITORY or NLS_DATE_LANGUAGE session parameters so it will always give the same answer (independent of the settings of the user who runs the query).

CodePudding user response：

You can use such a combination

SELECT TO_CHAR(dt,'HH24:MI:SS','NLS_DATE_LANGUAGE=English') AS Hour, 
       TO_CHAR(dt,'Day','NLS_DATE_LANGUAGE=English') AS day
  FROM t
 WHERE TO_CHAR(dt,'Dy','NLS_DATE_LANGUAGE=English') IN ('Tue','Wed','Thu','Fri')
   AND TO_CHAR(dt, 'HH24:MI:SS') NOT BETWEEN '07:00:01' AND '08:00:00'
    OR TO_CHAR(dt,'Dy','NLS_DATE_LANGUAGE=English') = 'Mon'
   AND TO_CHAR(dt, 'HH24:MI:SS')>= '07:00:00'
    OR TO_CHAR(dt,'Dy','NLS_DATE_LANGUAGE=English') = 'Sat'
   AND TO_CHAR(dt, 'HH24:MI:SS')<= '08:00:00'

where needs to consider restricting the periods for the bound dates individually

Demo