Getting dynamic date in oracle SQL query executed from python environment using cx_Oracle package

Question

I'm trying to write a query which fetches the current date and gets the relevant log data. Have written a nested query which works from PL/SQL but it isn't working from Python environment. Please advise.

Working query for PL/SQL:

SELECT * FROM TBLIRISVISITLOGS TLOG
WHERE TLOG.IVL_VISITDATE = (SELECT TO_CHAR(SYSTIMESTAMP,'DD-MON-YYYY') FROM DUAL)

When I execute this query in Python environment, it fetches an empty table.

Why don't you simply use WHERE TLOG.IVL_VISITDATE = CURRENT_DATE? — dnoeth
– dnoeth, Commented Apr 10, 2019 at 16:28
Because this script will run on a daily basis, I need to get current date for that day only. — Hisham Sajid
– Hisham Sajid, Commented Apr 10, 2019 at 17:36

Hisham Sajid · Accepted Answer · 2019-04-10 17:38:19Z

1

seems like I've found the solution:

from pandas import Timestamp as tstamp
test_query = """
SELECT * FROM TBLIRISVISITLOGS T WHERE T.IVL_LATITUDE>0 AND T.IVL_VISITDATE = TO_DATE('{CURR_DATE}','YYYY-MM-DD')
""".format(CURR_DATE=str(tstamp.now().date()))

answered Apr 10, 2019 at 17:38

Hisham Sajid

3013 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dnoeth Over a year ago

Same comment: Why don't you simply use WHERE TLOG.IVL_VISITDATE = CURRENT_DATE?

Hisham Sajid Over a year ago

Because it is part of an automated script that will run periodically every day, it will get data for only that day.

dnoeth Over a year ago

CURRENT_DATE is Standard SQL's a placeholder for today's date, it's the same as tstamp.now().date().

Hisham Sajid Over a year ago

Oh. I didn't know, I'll try this and let you know.

Collectives™ on Stack Overflow

Getting dynamic date in oracle SQL query executed from python environment using cx_Oracle package

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related