Why does the DATE() function return the next date after 6:00 PM?

Reply
Highlighted
Black Belt

Why does the DATE() function return the next date after 6:00 PM?

I have a dataset with a Date and Time stamp field.  When I convert it to a date field using DATE(`date_field`) I am finding some unexpected results.

1.png

 

 

The only way that I was able to get the intended results was to subtract 6 hours from the timestamp first:

Is this an intended function of the DATE() function, or am I doing something wrong?


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________

Accepted Solutions
Major Red Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

Have you tried using the Convert_TZ() fundtion  as a second argument ?

here it is an simple example of how to use it

 

DATEDIFF(Date(CONVERT_TZ(now(),'UTC','EST')),`TransactionDate`)

this give us the number of days from current date/time

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Major Red Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

The CONVERT_TZ function automatically accounts for daylight savings when using long time zone names

 

for example:

CONVERT_TZ(`Date`, '+00:00', '+10:00')

CONVERT_TZ(`Date`,'UTC','American/Chicago')
Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Major Blue Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

Try this one out.

convert_tz(`Date and time of Last Change`,'utc','us/central')


-----------------
Chris

All Replies
Major Red Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

It does the same thing on my flows so we have to adjust for UTC time since we are in EST time,  always substracting 4 or 5 hours depending of daily savings time to prevent that issue

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Black Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

How do you determine the number of hours to subtract though?  I would like to prevent the need to change my dataflow every 6 months or so


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Major Red Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

Have you tried using the Convert_TZ() fundtion  as a second argument ?

here it is an simple example of how to use it

 

DATEDIFF(Date(CONVERT_TZ(now(),'UTC','EST')),`TransactionDate`)

this give us the number of days from current date/time

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Black Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

I feel like this runs into the same issues.  At some point you would need to change from 

CONVERT_TZ(now(),'UTC','EST')

to

CONVERT_TZ(now(),'UTC','EDT')

I can't find a way to automate the switch, given that the actual date changes each year


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Major Red Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

The CONVERT_TZ function automatically accounts for daylight savings when using long time zone names

 

for example:

CONVERT_TZ(`Date`, '+00:00', '+10:00')

CONVERT_TZ(`Date`,'UTC','American/Chicago')
Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Black Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

I thought we had a solution here, but I'm not able to get the long timezone name to work:tz.png

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Major Blue Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

Try this one out.

convert_tz(`Date and time of Last Change`,'utc','us/central')


-----------------
Chris
Black Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

Thank you @cwolman  and @Godiepi 

 

I'm not sure how many answers I can accept as a solution, but I've got this conversion working now.  

ty sman.png

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Major Red Belt

Re: Why does the DATE() function return the next date after 6:00 PM?

@ST_-Superman-_  its got to take more than one to be able to help the man of steel Smiley Happy  (Superman theme song playing in the backgorund)

 

@DaniBoy  should take @cwolman  and me up a Belt in the Dojo for doing this LOL !

 

Have a good day guys

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!