CR 8.5, SQL time field imports 0:01 as 65536 instead of HH:MM

[BlackSupra]

We are using Dharam ODBC SDK, and in Crystal Reports 8.5 the 'time' data-type imports as a large number that is usually a multiple of 65536, instead of hours:minutes.

ex. 00:01 = 65536
00:30 = 1966080
01:30 = 1966081
10:30 = 1966090

Here is the formula I use to adjust the time

for the time of day...
----------------------------------------------
numberVar dur := {sql_table.event_time};
numberVar hrs;
numberVar min;
stringVar hhmm;
stringVar am_pm;

min := round(dur/65536);
hrs := dur-(65536*min);

if hrs >= 12 then
am_pm := "pm"
else
am_pm := "am";

if hrs > 12 then hrs := hrs - 12;

hhmm := totext(hrs, "00") + ":" + totext(min, "00") + am_pm;

hhmm;
--------------------------------


for a duration (staff_time, client_time...)
----------------------------------------------
numberVar dur := {sql_table.event_duration};
numberVar hrs;
numberVar min;
stringVar hhmm;

min := round(dur/65536);
hrs := dur-(65536*min);

hhmm := totext(hrs, "00") + ":" + totext(min, "00");

hhmm;
--------------------------------

This is a variation of the seconds->ddhhmmss formula from tek-tips.

My database lets the data-type for any TIME to be
a) time - the above mentioned oddity.
b) datetime - forces "01/01/1990 00:00.00" which CR8.5 reads as a string.
c) minutes - just a interger of minutes, 01:20 becomes 80

CR10 reads a) and b) as date/time and "format field" works properly.

Hope this helps somebody.

--Robert