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

My Date formula is subtracting 2 days instad of 1 day

collinjd
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?

9 REPLIES 9
andyuttley
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 

BenMoss
ACE Emeritus
ACE Emeritus

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 

BenMoss
ACE Emeritus
ACE Emeritus

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.

andyuttley
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...

 

BenMoss
ACE Emeritus
ACE Emeritus

You aren't missing anything!

andyuttley
11 - Bolide
11 - Bolide

@BenMoss wrote:

You aren't missing anything!


First (Date)time for everything...

collinjd
8 - Asteroid

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

MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
BenMoss
ACE Emeritus
ACE Emeritus

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