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

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

Interface Tools

Accepted answers

echuong1

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.

All comments

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

configuration.PNG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703