Hello, I am trying to get the average time out of a datetime column. I am able to get the average by casting to a numeric data time. Here is how I'm doing that:
AVG(CAST(CAST(CONVERT(char(8), datetimetoavg, 108) as datetime)
as numeric(18, 4)) * 24)
So this will give me an average number that looks like this: 12.006089 or
which is 12pm or 12:25am respectively
now I'd like to take these numbers and convert them back to a proper datetime format. I've tried:
right(convert(datetime, AVG(CAST(CAST(CONVERT(char(8), datetimetoavg, 108) as datetime)
as numeric(18, 4)) * 24), 112), 8)
But this doesn't seem to be distinguishing between am or pm.
Would anyone be able to help me convert the numeric time value back to a datetime format or has any other ideas to find the average time from a datetime column?