Using a DateTime Filter in an Oracle SQL Query via Dynamic Input

[alexnajm]

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!

[mceleavey]

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.

[alexnajm]

Yes I can confirm it is in DateTime format!

[alexnajm · Accepted solution]

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 🙂