I need to fetch the records that are between these DATE and DATE

I have a AUDIT Table in Oracle with DATE_AND_TIME as DATE and DATE_AND_TIME_24HOUR_FORMAT as Varchar2. I need to fetch records that are between two Date and DATE_AND_TIME_24HOUR_FORMAT. for example

Record  Date_AND_TIME   DATE_AND_TIME_24HOUR_FORMAT 
1   02-Feb-2022 13:35:24    
2   02-Feb-2022 13:35:56    
3   07-Feb-2022 13:35:32    
4   07-Feb-2022 13:35:23    
5   07-Feb-2022 13:35:02    
6   07-Feb-2022 13:36:02

I need the Query which will fetch me records 3,4,5 if I execute it for 03-FEB-2022 13:35:00 to 07-FEB-2022 13:35:00 and record 6 if I execute the query for 07-FEB-2022 13:36:00 to 12 07-FEB-2022 13:37:00.

CodePudding user response：

Not sure why you have used the column's datatype like this. You can directly use the timestamp or Date column only which will contain the DateTime information in it. If you cannot change the datatype, You can use below query -

SELECT *
  FROM AUDIT
 WHERE Date_AND_TIME || ' ' || DATE_AND_TIME_24HOUR_FORMAT 
       BETWEEN TO_DATE('03-FEB-2022 13:35:00', 'DD-MON-YY HH24:MI:SS') 
           AND TO_DATE('07-FEB-2022 13:35:00', 'DD-MON-YY HH24:MI:SS');

CodePudding user response：

Do not store your time values in a VARCHAR2 column. In Oracle, a DATE data type ALWAYS has the components year, month, day, hour, minute and second so you can store the time in the DATE column.

CREATE TABLE table_name (
  Record        NUMBER PRIMARY KEY,
  Date_AND_TIME DATE
);

Then:

INSERT INTO table_name (record, date_and_time)
SELECT 1, TO_DATE('02-Feb-2022 13:35:24', 'DD-MON-YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2, TO_DATE('02-Feb-2022 13:35:56', 'DD-MON-YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 3, TO_DATE('07-Feb-2022 13:35:32', 'DD-MON-YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 4, TO_DATE('07-Feb-2022 13:35:23', 'DD-MON-YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 5, TO_DATE('07-Feb-2022 13:35:02', 'DD-MON-YYYY HH24:MI:SS') FROM DUAL UNION ALL
SELECT 6, TO_DATE('07-Feb-2022 13:36:02', 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

Then your query is simply:

SELECT *
FROM   table_name
WHERE  date_and_time BETWEEN TO_DATE('03-FEB-2022 13:35:00', 'DD-MON-YYYY HH24:MI:SS')
                     AND     TO_DATE('07-FEB-2022 13:36:00', 'DD-MON-YYYY HH24:MI:SS')

SELECT *
FROM   table_name
WHERE  date_and_time BETWEEN TO_DATE('07-FEB-2022 13:36:00', 'DD-MON-YYYY HH24:MI:SS')
                     AND     TO_DATE('07-FEB-2022 13:37:00', 'DD-MON-YYYY HH24:MI:SS')

db<>fiddle here