Skip to content
Advertisement

Oracle Query With (BETWEEN Two Dates) Returns Empty Result Set

I have an Oracle SQL Query I am trying to run, but it keeps returning null value for the second query below but when I use the first query it returns a value. Please can someone help me check what I may be doing wrong in query 2? Note that the date column is in the Format 21/09/2020 10:00:00 AM and I want to get all records from 22-SEP-2020 11:00:00 AM to the current datetime for query 2. I am implementing query 2 for a Service, reason why I want to stick to it.

  1. SELECT query_date from Users where query_date > TO_DATE('21-SEP-2020 10:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM')

  2. SELECT query_date from Users where query_date BETWEEN TO_DATE('21-SEP-2020 10:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM') AND TO_DATE('22-SEP-2020 5:46:00 PM', 'DD-MON-YYYY HH:MI:SS PM')

Advertisement

Answer

If you have to use BETWEEN, why not use sysdate?

WHERE query_date BETWEEN TO_DATE('21-SEP-2020 10:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM') AND sysdate
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement