Python: cx_Oracle does not like how I am entering date

I am trying to do a simple select all query in python using the Cx_oracle module. When I do a select all for the first ten rows in a table I am able to print our the output. However when I do a select all for the first ten rows for a specific date in the table all that gets printed out is a blank list like this: [].

Here is the query select all query that prints out all the results:

sql_query = "select * from table_name fetch first 10 rows only"
cur = db_eng.OpenCursor()
db_eng.ExecuteQuery(cur, sql_query)
result = db_eng.FetchResults(cur)
print(result)

The above query works and is able to print out the results.

Here is the query that I am having trouble with and this query below works in sql developer:

sql_query = "select * from table_name where requested_time = '01-jul-2021' fetch first 10 rows only"
cur = db_eng.OpenCursor()
db_eng.ExecuteQuery(cur, sql_query)
result = db_eng.FetchResults(cur)
print(result)

I also tried this way where I define the date outside of the query.

specific_date = '01-jul-2021'
sql_query = "select * from table_name where requested_time = '{0}' fetch first 10 rows only".format(specific_date)
cur = db_eng.OpenCursor()
db_eng.ExecuteQuery(cur, sql_query)
result = db_eng.FetchResults(cur)
print(result)

Answer

Oracle dates have a time portion. The query

select * from table_name where requested_time = '01-jul-2021' fetch first 10 rows only

Will only give you the rows for which the value for the column requested_time is 01-jul-2021 00:00. Chances are that you have other rows for which there is a time portion as well. To cut off the time portion there are several options. Note that I explicitly added the a TO_DATE function to the date – you’re assuming that the database is expecting a dd-mon-yyyy format and successfully will do the implicit conversion but it’s safer to let the database know.

1. TRUNC truncate the column – this will remove the time portion

SELECT * 
  FROM table_name 
 WHERE TRUNC(requested_time) = TO_DATE('01-jul-2021','DD-mon-YYYY')
 FETCH FIRST 10 ROWS ONLY

2. Format the column date to the same format as the date you supplied and compare the resulting string:

SELECT * 
  FROM table_name 
 WHERE TO_CHAR(requested_time,'DD-mon-YYYY') = '01-jul-2021'
 FETCH FIRST 10 ROWS ONLY

Example:

pdb1--KOEN>create table test_tab(requested_time DATE);

Table TEST_TAB created.

pdb1--KOEN>BEGIN
  2  INSERT INTO test_tab(requested_time) VALUES (TO_DATE('08-AUG-2021 00:00','DD-MON-YYYY HH24:MI'));
  3  INSERT INTO test_tab(requested_time) VALUES (TO_DATE('08-AUG-2021 01:00','DD-MON-YYYY HH24:MI'));
  4  INSERT INTO test_tab(requested_time) VALUES (TO_DATE('08-AUG-2021 02:10','DD-MON-YYYY HH24:MI'));
  5  END;
  6  /


PL/SQL procedure successfully completed.

pdb1--KOEN>SELECT COUNT(*) FROM test_tab WHERE requested_time = TO_DATE('08-AUG-2021','DD-MON-YYYY');

  COUNT(*)
----------
         1
--only 1 row. That is the rows with time 00:00. Other rows are ignored

pdb1--KOEN>SELECT COUNT(*) FROM test_tab WHERE TRUNC(requested_time) = TO_DATE('08-AUG-2021','DD-MON-YYYY');

-- all rows

  COUNT(*)
----------
         3