Convert number to Date

[JSEH]

Hi,

When i download the data from one of the applications the datetime (5/29/23, 8:48 PM) is format is number  1685357301

How can i convert 1685357301 to proper datetime which is 5/29/23, 8:48 PM

I tried the solution in the link below but did not work

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Convert-Number-Format-into-Dat...

I get the error "ConvError: Formula (7): TODATE: ToDate(...) was given a number beyond the maximum of 2958465 or below the mininimum of -182619."

Thanks

JS

[Luke_C]

Hi @JSEH 

This format is called epoch time and is the number of seconds since 1970. Here's a thread to show the formula you should use: 

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Convert-Epoch-time-to-actual-d...

[jrgo]

@JSEH 

The Formula expression below would convert that EPOCH date/time value into a standard date/time format

DATETIMEADD('1970-01-01 00:00:00', [epoch], 'seconds')

Note that EPOCH time is typically represented in UTC TZ. So after you apply, you may need to add another DATETIME... function to reflect it in the desired timezone.

Below is an example of using the DateTimeToLocal() function to convert it to my local time, which is based on my machines time setting.