Converting 7599128320 to = 01/22/2008 07:52

[chiplarsen]

I am trying to create a formula in Crystal 10. The database is Oracle 9i. I have a field in my database that is stored as a number. I am trying to create a formula to convert that number to a Date Time. I know that 7599128320 = 01/22/2008 07:52. I just can not figure out how to get my formula to come up with that date time. I know that there are 86400 seconds in a day. I have tried to use that in a formula along with the mod function, but I am stuck.
Can anyone help me get started? Thank you.

 

[PatriciaObreja]

http://www.tek-tips.com/viewthread.cfm?qid=1446210&page=1

Is this what you need?
The logic should be like this:
1 min = 60 sec;
1 hr = 3600 sec;
1 day = 86400 sec;

NumberOfDays = NumberOfSeconds \ 86400
NumberOfSeconds = NumberOfSeconds Mod 86400

NumberOfHours = NumberOfSeconds \ 3600
NumberOfSeconds = NumberOfSeconds Mod 3600

NumberOfMinutes = NumberOfSeconds \ 60
NumberOfSeconds = NumberOfSeconds Mod 60

"\" performs integer division, and mod returns the remainder of the division.

The result will be:
NumberOfDays : NumberOfHours : NumberOfMinutes : NumberOfSeconds

 

[chiplarsen]

I can sort of follow it, but I still can not get back to the date 01/22/2008. Thank you for the quick response. I have to be missing something. Sooner or later, the lightbulb is going to go off..

 

[lbass]

Are you sure that number is correct? I think we'd have to see a sample of numbers and the datetimes they correspond to before we could figure this out.

-LB

 

[IdoMillet]

Assuming the number is Epoch (number of seconds since 1970/01/01), use:

DateAdd("s", {your_epoch_number}, datetime(1970,01,01))

- Ido


view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[chiplarsen]

Thanks for the reply.
The number must not be a Epoch number. I used that formula and came with 8/09/1938 8:02:08. The number,7599128320, should have returned a value of 01/22/2008 07:52. Thanks for the reply.

 

[lbass]

That's why I asked for a sample. The number doesn't translate to the usual dates.

-LB

 

[chiplarsen]

Example
7599128320 translates to 01/22/2008 07:52
7599926700 translates to 1/30/2008 07:25

First Example
75991 = 1/22/2008
28320 = 07:52

Second Example
75999 = 1/30/2008
26700 = 07:25


Now the hard part is trying to get a formula to calculate that data for us. Thank you for the help.

 

[chiplarsen]

I did figure out that 1/30/2008 is 75999 days from 01/01/1800. 75999 is the first 5 digits in my second example. I used this fomula to figure that out.

DateDiff ('d',#01/01/1800# ,#01/30/2008# )

 

[lbass]

Try this:

stringvar x := {table.number};
datetime(date(dateadd("d",val(left(x,5)),date(1800,1,1))),
time(dateadd("s",val(mid(x,6)),currentdate)))

-LB