Need help with converting date 1

[jpeters01]

I am using Crystal XI to pull data from an Oracle database. I have a SQL (that I am using for reference) where the date field {TABLE.START_DDT}, which is a number field, is converted to a date using ddt.todate{TABLE.START_DDT}. I have not been able to figure out how to do this in Crystal. When I pull the field onto my report it displays as 7,872,063,180.00, the date is 07/13/15.

Thanks so much for your help!

Jan

 

[SkipVought]

Hi,

ToDate() converts a DATE STRING from an ER, uh, um... STRING to a DATE.

I think that what you have is the number of SECONDS since 1970/01/01 00:00:00 which is how Oracle stores time stamps.

https://docs.oracle.com/cd/E17952_01/refman-5.5-en/date-and-time-functions.html

I don't know why you have what appears to be SECONDS rather than a DATE DISPLAY. Every time I've used dates in Oracle, I get a DATE DISPLAY.

 

[SkipVought]

When I pull the field onto my report it displays as 7,872,063,180.00, the date is 07/13/15.

How do you know that this number is equivalent to 07/13/15?

I'm doubting that it's an Oracle time stamp value.

 

[jpeters01]

SkipVought, It is not a time stamp, it is a start date/time for an order placed in an application. For example, when a physician orders a medication and they do not want it to be administered until tomorrow, they enter a start date/time of 7/23/15 at 0900. I ran my Crystal report for another patient, and verified in the application (and on a standard report in the application) that a Medication order scheduled to start 7/13/15 09:00 displays a Start Date of 7,872,032,400.00 on my Crystal report.

 

[jpeters01]

...and to clarify, I need both the Start date and start time to display as 7/13/15 09:00 on my report.

Thanks so much for your help!

Jan

 

[kray4660]

I am not sure if this helps, but it appears (a little guessing involved here) that the value 7,872,032,400.00 is the number of seconds since 1/1/1753 (minimum date for a MS-SQL datetime). Not sure how to get Crystal to do any conversion.

 

[jpeters01]

Yeah, I do not know how to do this either. I have not been able to find an answer anywhere...thanks though!

 

[BettyJ]

As SkipVought wrote, How do you know that this number is equivalent to 07/13/15?

Also another thought I had is - are you taking the correct data field.

Can you send 2-3 more sample dates displayed as numbers other than 7,872,063,180.00?

 

[jpeters01]

Yes, this is the only date field that is available. I found out that the first 5 numbers are the number of days since 01/01/1800, and the last 5 numbers are the seconds since midnight today (midnight is always nnnnn00000).

 

[AndyMc]

@Date formula:
dateserial(1800,1,1+val(left(totext({TABLE.START_DDT},0,""),5)))

@Time formula:
TimeSerial(0,0,0+val(right(totext({TABLE.START_DDT},0,""),5)))

 

[jpeters01]

AndyMC - this worked perfectly...thank you so much!!!