Help with converting EPOCH Time......ASAP

[MrHelpMe]

Hello,

I need to convert epoch time to normal date and time. I have a text field that holds the value for example 1017255877. I need to convert this to datetime. Does anybody know how to do this. I am using crystal 8.0. I am not really familiar with epoch time but our remedy administrator has called it this...something relating to a unix format. She says the epoch time...the field that is showing represents the # of seconds from Jan. 1, 1970. Thanks everyone

 

[IdoMillet]

Use the DateAdd function to Add that number of seconds to Jan. 1, 1970 .

hth,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[mbarron]

Your foumula would be:

dateadd("s",tonumber({EPOCH.TIME.FIELD}),date(1970,01,01)) Mike

 

[MrHelpMe]

Thanks you very much everyone,

Mbarron could you explain what the "s" means in this statement. I need to explain this to someone else. It works perfectly.

Next question for both of you. How do I incorporate this formula into a daterange parameter. Nobody will no how to input the epoch time as a selection for the date range. Any idea how to get the user to choose a normal data for the parameter prompt. Thanks again and thank you for the quick response.

Salvatore

 

[IdoMillet]

"s" is for seconds.

Set up the prompt as date and in the record selection formula create the necessary expression to compare it to the result above.

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[MrHelpMe]

Sorry IdoMillet,

Not sure what you mean by this. I cannot use this formula field as a parameter(or can I). So how do I set it up as date in my parameter. The field is a text field not a date. Sorry maybe I am misunderstanding. How would I do this?

Salvatore.

 

[lyanch]

Set the above up as a SQL expression instead of a formula. Then it is easy to use as a criteria.

Lisa

 

[mbarron]

Here's how I would do it.
I'm assuming you're using a range for your date parameter.

Create a formula to change the EPOCH to a number:
{@epoch number}
tonumber(epoch)

Create two fields to calculate the seconds elapsed since Jan 1, 1970 - one for the minimum date:
{@min date}
datediff("s",date(1970,1,1),minimum({?date range}))

and one for the maximum:
{@max date}
datediff("s",date(1970,1,1),maximum({?date range}))

Then for the select expert:
{@epoch number} in {@min date} to {@max date}

This will limit your result to EPOCH numbers between the two dates Mike