Using an action tool to update Date AND Time in sql query in workflow

[kas]

Hello,

I have a SQL query(in an input tool) that requires a date time field to be updated in the WHERE statement.

If it were just the date I'd  have no problem using the Date interface tool to update it, but not when there is a time stamp. There is no actual time, just zeros, but thery're required in order to have the query run.

For example, the line in the query to be updated is: PROD_USIG_STND_VW.AGMT_VAL_CMN_VW.AGMT_VAL_AS_OF_DT = '2019-11-25 00.00.00.000000' 

When I run it I get no results back (I do if I  manually change the query).

Is there a way to use an action tool to change the date?

Thank you,

Kim

[benakesh]

Hi @kas ,

You can change datetime  to  date field by  'cast'  and   interface tool  can change  the date  .  

Add connection name  to attached wf   to try the app.

[CharlieS]

Change the Action to Update Value with Formula and try something like this that adds the time portion:

Replace([Destination],"2019-11-25",[#1]+" 00.00.00.000000")

[echuong1]

Alternatively, you can use a Text Interface tool and have the user manually enter dates (ex: 2019-11-25). You can configure the action tool to only update the date part of the query. 

[kas]

@benakesh 

Unfortunately I'm not using database tools, I'm using an input tool connecting using odbc and I hadn't clarified, the date/time is in the where statement.

[kas]

@echuong1 

That had been my first thought and try, but I still didn't achieve results.

It's possible I have something else setup wrong? Here's how I have the action tool configured

[echuong1 · Accepted solution]

Your configuration looks correct...Have you tried running the workflow in debug mode to see if everything is getting updated? 

Maybe try using update value with formula instead. 

[kas]

@echuong1 

Ugh...simple mistake...once I opened the Interface designer I realized I had forgotten to select an output option (browse tool in this case)...Thank you!!

Kim

[prasannaJ]

Hi I am experiencing  a very similar problem but little strange, Below is a screenshot of my workflow, I have connected to HANA view using a SQL script. but when i tried to use "Replace" its not getting replaced and its not giving out any error too. This is strange. 

[ak2018]

Thank you for suggesting the replace formula. worked like a charm. For those who are struggling, I have a query going against Big Query ODBC and needed to be able to specify the start and end dates and a few other dates. This is just fantastic.