Oracle numbers to a date

[wgechter]

I am not very good at programming so I need a little help.
I am pulling from Oracle. There are 4 number fields that are supposed to be two dates and two time. When I pull I get the number 55,854 which is a date and the number 26,... for the time. I need to know how to convert these to legible dates in crystal. Any help would be appreciated. Thanks, Wendi

 

[mbarron]

Being unfamiliar with Oracle's "methods", it's impossible to know how to help without the following information.

In the Oracle environment: What date does the 55,854 represent? What time does the 26,... give you? Mike

 

[wgechter]

I beleive the 55,854 was yesterday's date, so March 18, 2003 and the 26 thousands were times of the day around noon to one, I think. But this could be wrong because I pulled information two weeks ago and they have the same number as the date of stuff I pulled yesterday(55,854). Doesn't make sense. Thanks for the help,
Wendi

 

[mbarron]

A couple of assumptions coming at you.
I'm assuming that:
55,584 is the number of days from 4/15/1850 (why that day, I don't know. I subtracted 55,584 days from yesterday)
26,000 is the number of seconds from midnight.

If these assupmtions are true (I'm having doubts because of the next to last sentence in your last post.But this could be wrong because I pulled information two weeks ago and they have the same number as the date of stuff I pulled yesterday(55,854)

The formula gives a datetime field of 3/18/03 7:13:20am for the numbers you gave.
numbervar times:= 55,584
numbervar dates:= 26,000
datevar dt;
timevar tm;
datevar startdate:=date(1850,4,15);


dt:=date(dateadd("d",dates,startdate));
tm:=time(dateadd("s",times,datetime(0,0,0,0,0,0)));

datetime(year(dt),month(dt),day(dt),hour(tm),minute(tm),second(tm))


Mike

 

[wgechter]

Thanks, I actually found today's date. It is 55,960 but I can put that into your formula above. 29,370 was about 8:00 this morning. Thanks so much. I'll let you know how it goes. Thanks again,
Wendi

 

[mbarron]

You may have noticed already, but I had the times number and the dates numbers reversed. With the new info, it appears that the start day is Jan 1, 1950 - (in order for the formula to work correctly, I had to start the count on 12/31/1849)

Corrected formula:

numbervar times:= 29730;
numbervar dates:= 55960;
datevar dt;
timevar tm;
datevar startdate:=date(1849,12,31);


dt:=date(dateadd("d",dates,startdate));
tm:=time(dateadd("s",times,datetime(0,0,0,0,0,0)));

datetime(year(dt),month(dt),day(dt),hour(tm),minute(tm),second(tm)) Mike

 

[wgechter]

With the formula above I produced today's date, unfortunately 55854 thru 55960 all produce today's date. I am going to work with it but thank you for a start, I had no idea.
Wendi

 

[mbarron]

Did you remember to change the 29730 and the 55960 to reflect your date and time fields?

ex:
numbervar times:= {time.field1};
numbervar dates:= {date.field1}
Mike

 

[wgechter]

Mike thanks so much. I've got exactly what I want. You have no idea how much that helped. Thanks again and again,
Wendi