ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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?

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
17 - Castor
17 - Castor

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
17 - Castor
17 - Castor

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
17 - Castor
17 - Castor

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
19 - Altair
19 - Altair
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.
Please Subscribe to my youTube channel.
BenMoss
17 - Castor
17 - Castor

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