Convert time_in field from number to standard time

[DougB13]

I need to convert the time_in and time_out data from a number to standard time. Number currently displays as 10 digit number, eg. 1222167895, into standard time 11:12:00pm. Does anyone have a formula for this conversion.

 

[DougB13]

This is for Crystal Reports XI.

 

[DougB13]

I believe the number represents date and time so I need to have the output display in standard date & time, 10/21/2008 11:12:00pm. Any help would be great.

 

[kskid]

Can you elaborate on what the number represent? It's not a julian date for 10/21/2008 11:12:00 PM with a value of 988295.46667

 

[DougB13]

The number represents the Time for Call 1 from a remote system and call 2. A system auto-generated report the data from db actually displays as 10/21/08 11:12. The number in the db is in the following format 1222167895. We are looking to create it in crystal so we can kick it off anytime and distribute.

 

[DougB13]

I think the format on the db might actually be unix time.

 

[kskid]

Using the both online converters,

http://www.epochconverter.com/

http://www.onlineconversion.com/unix_time.htm

the results are different than yours

1222167895 is returning Tue, 23 Sep 2008 11:04:55 UTC

 

[kskid]

I am an Oracle user and I am not aware of any functions in Crystal that will convert the unix time to standard time, you might be able to get some help from other UNIX users with same conversion issue.

Good Luck.

 

[DougB13]

Actually what is you showed as being returned might work.

 

[kskid]

If you know the algorithm behind the conversion, then you can probably create a formula to calculate the date. Otherwise, it is not available in Crystal.

 

[kskid]

Did some digging. Apparently the number represents the number of seconds that has elapsed since midnight on December 31, 1969 or the beginning of 1/1/1970 00:00:00

If that is the case, then your formula would be

dateadd("s",{table.unixnumber},datetime(1970,1,1,0,0,0))

-lw

 

[kskid]

Here's my source of information with description

http://www.unixtimestamp.com/index.php

What is the unix time stamp?
The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970. Therefore, the unix time stamp is merely the number of seconds between a particular date and the Unix Epoch. This is very useful to computer systems for tracking and sorting dated information in dynamic and distributed applications both online and client side.

What happens on January 19, 2038?
On this date the Unix Time Stamp will cease to work due to a 32-bit overflow. Before this moment millions of applications will need to either adopt a new convention for time stamps or be migrated to 64-bit systems which will buy the time stamp a "bit" more time.

 

[DougB13]

The output is displaying 4 hour ahead of the actual output. What needs to be changed on the formula. I am new to crystal and not sure of creating formulas. Thank You!

 

[csera]

I am running Crystal Reports 10 and am also trying to do this conversion - used the formula listed in this thread and also some other versions I found on the web and my date/time results are not only hours off, but months off. I found some comments on the web that there is a new DLL file needed with the EpochtoDateTime formula? has anyone heard of this and where I can get this DLL file?
Thanks