Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

My Date formula is subtracting 2 days instad of 1 day

Highlighted
8 - Asteroid

Hi,

 

I have a formula below to return the date and time as today minus 1 day. However, it is returning the date minus 2 days.

 

Formula below run today (26th June 2019 is returning the date as 24th June 2019)

 

DateTimeFormat(DateTimeToUTC(DateTimeAdd (DateTimeToday(), -1,"days")),"%m/%d/%Y")

 

Any idea what why this is returning -2 days?

Highlighted
11 - Bolide
11 - Bolide

Hi @collinjd 

 

That will be because you don't have a time associated with your original date (it's a date, rather than a datetime - so it's probably assuming it has JUST turned 26 June). If you include the time it will work correctly (see attached workflow) 

 

Hope that helps

 

Thanks

 

Andy 

Alteryx Certified Partner
Alteryx Certified Partner

hmm;

 

This is an interesting obersrvation, just by breaking down your formula the issue is definitely with the datetimetoUTC() function.

 

At present it's returning "2019-06-25 23:00:00"

 

Which, according to my googling skills, is definitely not correct.

 

Thus when you do datetimetoday(),-1,'days') then it's returning 2019-06-25, then converting that to UTC is doing another day.

 

Let's tag the master himself @MarqueeCrew 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

ARRR, yes; just follow @andyuttley 's guidance.

 

It appears a datetimetoday() returns todays date at 00:00:00, then the conversion happens with that timestamp.

Highlighted
11 - Bolide
11 - Bolide

sure I'm missing something, but is it not the use of DateTimeToday where it should be DateTimeNOW()? 

 

e.g.:

In the UK, UTC is -1hr. So Alteryx's logic would be:

today = 26 June, 00:00:00 (time today I think resolves to midnight as opposed to NOW)

-1 day = 25 June, 00:00:00

-1 hr = 24th...

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

You aren't missing anything!

Highlighted
11 - Bolide
11 - Bolide

@BenMoss wrote:

You aren't missing anything!


First (Date)time for everything...

Highlighted
8 - Asteroid

Great thanks I will change to DateTimeNOW() and yes that works. Thanks for your help!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Great job. Date functions with time become tricky. A note: I prefer DateTimeStart() to DateTimeNow() because it isn't volatile as job runs.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Great to hear this bought you to a resolution; can you please make sure you mark @andyuttley 's post as a solution (providing it has indeed bought you to one); this way, should people have a similar issue and search the community, this result will be prioritised

 

Ben

Labels