Alteryx Designer Desktop Discussions

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

How to add or subtract minutes from a time?

ddiesel
13 - Pulsar
13 - Pulsar

How to I add or subtract minutes from a time field? 

 

In this case, I need to add 30 minutes to each of my "Start Times" to get the "End Times".

 

DateTimeAdd([Start Time],+30,"minutes") gives conversion error: DATETIMEADD: "04:00:00" is not a valid DateTime.

 

I can think of a couple workarounds but is there an easy way to do this?

 

Sample workflow:

 

14 REPLIES 14
ddiesel
13 - Pulsar
13 - Pulsar

... and thanks @KevinP! You both had the answer! 

jt_edin
8 - Asteroid

This is brilliant, thank you. But why does the date component need a space before the end quote? 

"2017-01-01 "

It doesn't parse without the space, yet I don't understand how I would've figured that out if it wasn't for @MarqueeCrew's brilliant suggestion. Any ideas what the space is for? Thanks 

KevinP
Alteryx Alumni (Retired)

@jt_edin The space is to ensure there is separation between the date and the time when appending the string values. Let's consider the formula both @MarqueeCrew and I previously posted:

 

DateTimeAdd("1970-01-01 "+[Start Time],30,"minutes")

The section '"1970-01-01 "+[Start Time]' takes the value of the field Start Time and appends it to the string '1970-01-01 '. Assuming Start Time is set to 12:00:00 this results in a value of '1970-01-01 12:00:00'. If the space wasn't present the date and time would end up run together like this '1970-01-0112:00:00'. This value without the space wouldn't be treated as a valid datetime by the DateTimeAdd() function. You can see/test this yourself in a formula tool with the following formulas

 

Formula with space:

"1970-01-01 "+[Start Time]

Formula without space:

"1970-01-01"+[Start Time]

Alternatively you could add the space separately but it would require a longer formula to get the same result as seen below:

DateTimeAdd("1970-01-01"+" "+[Start Time],30,"minutes")

 

 

amitsrivastava
6 - Meteoroid
 
MohithRai
8 - Asteroid

 

Labels