How to compare date with time in oracle

user_12261382 0
Viewed 114

Trying to compare two dates with time.But comparison is not working.

SELECT *
   FROM attendance
  WHERE     TO_DATE (checktime, 'DD/MM/YYYY HH:MI:SS AM') >=
               TO_DATE ('01/09/2019 04:30:00 PM', 'DD/MM/YYYY HH:MI:SS AM')
        AND TO_DATE (checktime, 'DD/MM/YYYY HH:MI:SS AM') <=
               TO_DATE ('30/09/2019 10:00:00 PM', 'DD/MM/YYYY HH:MI:SS AM')
        AND userid = '3825'
        AND SUBSTR (checktime, -2, 2) = 'PM'
ORDER BY TO_DATE (checktime, 'DD/MM/YYYY HH:MI:SS AM') ASC

I was expecting output equal or grater then 04:30 PM and less then or equal 10:00 PM.But this date comparison is not working.Here is the Output of Code.I want my result includes date and time between mentioned periods. Note:CHECKTIME datatype is varchar2.

oracle sql
2 Answers

You can fix your format by using such a format containing TO_TIMESTAMP conversion

SELECT *
  FROM attendance
 WHERE checktime 
       BETWEEN TO_TIMESTAMP('01/09/2019 16:30:00.000000','dd/mm/yyyy hh24:mi:ss.ff')
           AND TO_TIMESTAMP('30/09/2019 22:30:00.000000','dd/mm/yyyy hh24:mi:ss.ff')
   AND userid = 3825
 ORDER BY checktime;

Demo

EDIT : you had better to add a new column with timestamp datatype, and update your new column's data by using TO_TIMESTAMP conversion such as below : 

UPDATE attendance
   SET checktime2 = TO_TIMESTAMP('3/09/2019 5:38:36 PM','dd/mm/yyyy hh:mi:ss AM')  
 WHERE id = 3825
   AND checktime = '3/09/2019 5:38:36 PM'
user_58413060

I think you need data for all the days(01/09/2019 - 30/09/2019) and the time of the day should be between 04:30 PM and 10: PM.

You can achieve this using the following query:

SELECT
    *
FROM
    ATTENDANCE
WHERE
    TRUNC(TO_DATE(CHECKTIME, 'DD/MM/YYYY HH:MI:SS AM'))
        BETWEEN TO_DATE('01/09/2019', 'DD/MM/YYYY') 
        AND TO_DATE('30/09/2019', 'DD/MM/YYYY')
    AND ( TO_DATE(CHECKTIME, 'DD/MM/YYYY HH:MI:SS AM') - TRUNC(TO_DATE(CHECKTIME, 'DD/MM/YYYY HH:MI:SS AM')) ) * 1440  -- converting difference into minutes
        BETWEEN 990 -- 04:30 PM in minutes (16.5*60)
        AND 1320 -- 10:00 PM in minutes (22*60)
    AND USERID = '3825'
    AND SUBSTR(CHECKTIME, - 2, 2) = 'PM'
ORDER BY
    TO_DATE(CHECKTIME, 'DD/MM/YYYY HH:MI:SS AM') ASC;

Cheers!!

user_115656290
Related