Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

kas
8 - Asteroid

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' 

kas_0-1575656379609.png

 

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

9 REPLIES 9
benakesh
12 - Quasar

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.

 

benakesh_0-1575658319363.png

 

CharlieS
17 - Castor
17 - Castor

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
Alteryx Alumni (Retired)

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
8 - Asteroid

@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
8 - Asteroid

@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
Alteryx Alumni (Retired)

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. 

 

echuong1_0-1575660925014.png

kas
8 - Asteroid

@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
6 - Meteoroid

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. 

 

 

prasannaJ_1-1600238285194.png

 

 

prasannaJ_0-1600238141610.png

prasannaJ_2-1600238313315.png

 

ak2018
8 - Asteroid

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. 

Labels