Hello everyone! I have a SQL query in my Dynamic Input tool that I want to modify with a DateTime in the WHERE clause. However, it seems to only be considering the date and not the time when modifying the SQL query to replace a specific string. Here is the query (connects to an Oracle database) with the actual columns and such aliased for privacy:
SELECT A, B, C, Date
FROM X, Y
WHERE X.A = Y.A
AND Date > '01-Jan-1800'
The Dynamic Input has this configuration:
Modify SQL Query - SQL: Update WHERE Clause --> replace '01-Jan-1800' with '12-Nov-2020' (as an example)
The above works great with date alone, but when I try to add in time (i.e. '12-Nov-2020' to '12-Nov-2020 12:00:00'), the SQL errors out. After many To_Date and To_Timestamp tries, I figured I would ask the Community for some help. Thank you in advance!
Solved! Go to Solution.
Hi @alexnajm ,
can you confirm the source table to which you are applying the query is in a datetime format? if the column does not contain the timestamp then the datetime query will not match the mask of the source data.
M.
Yes I can confirm it is in DateTime format!
I found the answer to my own question here. For those who may want to know, this worked:
Date > TO_DATE(‘1801-01-01 00:00:00’,’YYYY-MM-DD HH24:MI:SS’)
Then Modify SQL Query - Replace a Specific String: 1801-01-01 00:00:00 with the field that has your datetime in the same format 🙂