SQL & CR8 DATETIME CALCULATIONS

[konad]

ok here is the deal, i am using MS SQL 7 and CR8, i am subtracting a date time from another date time field with this formula in CR8 using the fields from my database tables.
************************************************************
Datevar StatusCallTime := date({tblUnitRadioHistory.LastActionTime});
Datevar CallTime := date({tblDisHead.CallTime});
Numbervar SecondsBetweenDate := (StatusCallTime - CallTime) * 8640;

Numbervar SecBetweenTime := (Time({tblUnitRadioHistory.LastActionTime}) -
Time(0,0,0)) - (Time({tblDisHead.CallTime}) - Time(0,0,0));
SecondsBetweendate + SecBetweenTime / 60;
************************************************************
the problem comes in the ms sql database, it uses miliseconds, so there fore my select distinct is useless for reapeat records with the same LastActionTimes, because the milliseconds are returned with my SQL statements.
************************************************************
SELECT DISTINCT
tblDisHead.Dispatch#,
tblDisHead.CallTime,
tblUnitRadioHistory.RadioID,
tblUnitRadioHistory.CurrentDispatch#,
tblUnitRadioHistory.ColorCmd,
tblUnitRadioHistory.LastActionTime,
tblCallStatus.StatusCode,
tblCallStatus.StatusDescription,
tblCallStatus.StatusType
FROM
(CAD_demo.dbo.tblDisHead tblDisHead INNER JOIN CAD_demo.dbo.tblUnitRadioHistory tblUnitRadioHistory ON tblDisHead.Dispatch# = tblUnitRadioHistory.CurrentDispatch#) LEFT JOIN XAP_demo.dbo.tblCallStatus tblCallStatus ON tblUnitRadioHistory.ColorCmd = tblCallStatus.StatusCode
WHERE
tblCallStatus.StatusType = 'B' OR tblCallStatus.StatusType = 'U'
ORDER BY
tbldishead.Dispatch# ASC, tblUnitRadioHistory.LastActionTime ASC, tblUnitRadioHistory.RadioID ASC
************************************************************
do i need to somehow modify my sql statement, or my formula or what?

 

[dcm]

konad: Could you try to convert the datetime response from SQL to drop the milliseconds and then do a distinct on the seconds? David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[konad]

well there in lies the problem i have only worked with ms sql and cr8 for 2 weeks now and im basically a newbie programmer to boot