I have 2 columns (TotalDuration/ BartecDuration) and i need to get the avg duration of both column.
So far i have this
Select CONVERT(VARCHAR(255), CAST(AVG(CAST(CAST(TotalDuration AS DATETIME) AS DECIMAL(10,5))) AS DATETIME), 108) as TotalDuration, CONVERT(VARCHAR(255), CAST(AVG(CAST(CAST(BartecDuration AS DATETIME) AS DECIMAL(10,5))) AS DATETIME), 108) as BartecDuration from WasteCollectionStats
But the problem is i am only get the HH:MM:ss, and i need the milliseconds too.
Any help would be greatly appreciated :)
Answer by Kev Riley ·
That's because you are using style 108 - you need to use style 114
Answer by Oleg ·
I know I am a bit late for this train, but here is my solution which preserves the precision of the time(7) data type, which is 100 nanoseconds as @ThomasRushton has already mentioned in his comment. The problem with the query in question is that it is based on the conversion of the datetime to decimal and the former has the 3 milliseconds precision with rightmost millisecond digit limited to have only 3 possible values, namely 0, 3, and 7. At the same time, the attempt to increase the precision by using datetime2 will not work as the conversion from datetime2 to decimal is, naturally, not allowed (it will not make any sense to allow such conversion).
Instead, it is easy enough to convert the time(7) value into decimal format manually, by calculating the total number of seconds and preserving the decimal part representing the 100s nanoseconds (hours part times 3600 plus minutes part times 60 plus all seconds plus entire decimal part). Once the time(7) is converted like this and then aggregated, the resulting decimal number need to be unwound back to time(7). Here is the script:
;with means as ( select avg( datepart(hour, TotalDuration) * 3600. + datepart(minute, TotalDuration) * 60. + datepart(second, TotalDuration) + datepart(ns, TotalDuration) / 1000000000. ) TotalAsDec, avg( datepart(hour, BartecDuration) * 3600. + datepart(minute, BartecDuration) * 60. + datepart(second, BartecDuration) + datepart(ns, BartecDuration) / 1000000000. ) BartecAsDec from WasteCollectionStats ) select -- TotalAsDec, BartecAsDec, cast( format(cast(TotalAsDec as int) / 3600, '00:') + -- hours part format(cast(TotalAsDec - 3600 * (cast(TotalAsDec as int) / 3600) as int) / 60, '00:') + -- minutes part format(TotalAsDec % 60, '00.0000000') as time(7) -- seconds plus whatever decimals part ) AvgTotalDuration, cast( format(cast(BartecAsDec as int) / 3600, '00:') + format(cast(BartecAsDec - 3600 * (cast(BartecAsDec as int) / 3600) as int) / 60, '00:') + format(BartecAsDec % 60, '00.0000000') as time(7) ) AvgBartecDuration from means;
Hope this helps.