Hi All there,

I am checking the average/max/min delay of the messages sent. When I do this, I do miss out the milliseconds part.

I use Datediff() with seconds as the unit to find the same.

Eg: If the exact delay is 8 seconds and 678 milliseconds, I do miss out 678 milliseconds. If I go for "ms" with the datediff(), then I have to calculate it for seconds too. If the delay is in minutes, then I get a data overflow error, for the "ms" with datediff().

Is there any other way to get the exact delay?

regards,

-Vijay G

__________________
- Vijay G

DATEDIFF() returns the difference between two datetime values as measured by the number of the indicated intervals crossed. The result is an integer number of intervals.

If you use an interval value of 's' (seconds), then the function calculates the number of second intervals crossed between the two datetime values. Using 'ms' calclates the number of millisecond boundaries crossed, etc.

Thus, the result is an integer number of boundaries crossed. In other words, it calculates the difference for a single interval type only, and it is not possible to combine multiple types in the same operation. You'll have to convert the single interval type result yourself into multiple types.

If you use milliseconds, then integer dividing by 1000 converts to seconds, etc.

Since the result is an integer, using milliseconds allows you to express an interval of a bit less than 25 days before you get an overflow. If your total interval is more than that, you'd have to do some tricky manipulations involving normalizing the endpoints to two consecutive days and then adding in the whole number of days in milliseconds between the actual dates.

Not something I'd wnat to do in SQL...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Yes Jeff,

You are right. I have no other option. Should calculate it all from the frontend. I thought I would do it all from sql, and ask my support team to just use the query. But since these calculations are involved, I should give them a UI presenting the calculated data.

Thanks for you suggestions.

Cheers,

-Vijay G