Time Problem 1

[noslo]

Hello Helpful Ones,

I am using CR 8 and am a basic beginner. Here is my problem. I have a time field in my SQL database. When I create a report using this field, it changes it to a date. I looked at report options and the FAQs without result.

An example is 9:20:15 am in SQL is displaying as 12/30/1899 in CR. I have tried date time conversions in the formula editor without success. I know this is basic, I really could use some direction.

Grateful,

Helen Williams

 

[Naith]

Actually, this is a pretty good point, and certainly not basic at all.

Crystal (and I haven't tested this with 8.5, so if someone knows otherwise - by all means, shut me up) doesn't recognise database time fields. You have to convert the field at the database/query/stored procedure level to a string value.

Naith

 

[synapsevampire]

Use a SQL Expression to convert it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Noslo,

I have had this problem and what I do is fool it!

In a formula do this:

Time(currentdate + yourtimefield)

I had to this when I needed to calculate the difference between two times and used it in the datediff calc:

datediff("h", currentdate+timefield1, currentdate_timefield2)

will give the hours difference between the two!

I hope this was helpful...

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rogar]

The "12/30/1899" data value is probably a zero in the date part of a datetime field where the date is the integer part and the time is the fraction part of a floating point number. Maybe all you have to do is format the field into a time value (right-click on the field and select Format). If CR sees the field as a date type rather than datetime, it may not allow the time format. In this case, try using the CR function DateTime({YourField}) and format the result as above.

 

[synapsevampire]

If it's a new type of date/time field, such as Oracle 9i's new timestamp with timezone, etc., CR will choke on it.

Either convert it in a SP or a View, or create a SQL Expression to convert it.

You'll probably get the best performance and fewest bugs by letting the database do the work.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com