views:

1027

answers:

5

I've got a varchar field in SQL Sever 2005 that's storing a time value in the format "hh:mm"ss.mmmm".

What I really want to do is take the average using the built in aggregate function of those time values. However, this:

SELECT AVG(TimeField) FROM TableWithTimeValues

doesn't work, since (of course) SQL won't average varchars. However, this

SELECT AVG(CAST(TimeField as datetime)) FROM TableWithTimeValues

also doesn't work. As near as I can tell, SQL doesn't know how to convert a value with only time and no date into a datetime field. I've tried a wide variety of things to get SQL to turn that field into a datetime, but so far, no luck.

Can anyone suggest a better way?

+4  A: 

Try this

AVG(CAST(CAST('1900-01-01 ' + TimeField AS DateTime) AS Float))

You really should store those in a datetime column anyway. Just use a consistent date for that part (1/1/1900 is very common). Then you can just call AVG() and not worry about it.

Joel Coehoorn
On the subject of how the data is stored - believe me, if I was in charge that is how it would be stored. As it is, I just have to deal with the table as it exists.
Electrons_Ahoy
A: 

How do you think to average on datetime?

I guess that you need to GROUP BY some period (Hour?), and display Count(*)?

dmajkic
a datetime is just an 8-byte binary number. AVG should handle it just fine as long as all the date-parts are the same.
Joel Coehoorn
Nope. You get "Operand data type datetime is invalid for avg operator".But then why not AVG(CAST(YourStringAsDateTime AS INT))?
dmajkic
.. cos' you get only date part averaged. I ment to say that casting to FLOAT will be inacourate for fractions that are lost. It would be better to use NUMERIC() with higher precision.
dmajkic
+3  A: 

SQL Server can convert a time-only portion of a datetime value from string to datetime, however in your example, you have a precision of 4 decimal places. SQL Server 2005 only recognizes 3 places. Therefore, you will need to truncate the right-most character:

create table #TableWithTimeValues
(
    TimeField varchar(13) not null
)

insert into #TableWithTimeValues
select '04:00:00.0000'
union all
select '05:00:00.0000'
union all
select '06:00:00.0000'

SELECT CAST(TimeField as datetime) FROM #TableWithTimeValues
--Msg 241, Level 16, State 1, Line 1
--Conversion failed when converting datetime from character string.

SELECT CAST(LEFT(TimeField, 12) as datetime) FROM #TableWithTimeValues
--Success!

This will convert valid values into a DATETIME starting on 1900-01-01. SQL Server calculates dates based on 1 day = 1 (integer). Portions of days are then portions of the value 1 (i.e. noon is 0.5). Because a date was not specified in the conversion, SQL Server assigned the value of 0 days (1900-01-01), which accommodates our need to average the time portion.

To perform an AVG operation on a DATETIME, you must first convert the DATETIME to a decimal value, perform the aggregation, then cast back. For example

SELECT CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME) FROM #TableWithTimeValues
--1900-01-01 05:00:00.000

If you need to store this with an extra decimal place, you can convert the DATETIME to a VARCHAR with time portion only and pad the string back to 13 characters:

SELECT CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME), 114) + '0' FROM #TableWithTimeValues
Cadaeic
AHA! That was my problem - the number of places after the decimal. Thanks a million, Cadaeic.
Electrons_Ahoy
A: 

SQL Server stores datetime data as 2 4-byte integers, hence a datetime take 8 bytes. The first is days since the base date and the second is milliseconds since midnight.

You can convert a datetime value to an integer and perform mathematical operations, but the convert only returns the "days" portion of the datetime value e.g. select convert(int,getdate()). It is more difficult to return the "time" portion as an integer.

Is using SQL Server 2008 an option for you? That version has a new dedicated time data type.

Thanks, Andy.

Andy Jones
A: 

I'd work out the difference between all of the dates and an arbitrary point (01/01/1900), average it and then add it back on to the arbitrary point.

Andrew Hancox