Mean Time in SQL Sever 2005

Question

I have a varchar field in SQL Sever 2005 that stores the 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 these time values. However, this:

SELECT AVG(TimeField) FROM TableWithTimeValues

does not work, because (of course) SQL will not average varchars. However this

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

doesn't work either. As far as I can tell, SQL does not know how to convert a value with just the time and date in the datetime field. I tried a variety of things to get SQL to turn this field into a datetime, but so far no luck.

Can anyone suggest a better way?

Answer 1

SQL Server - -, 4 . SQL Server 2005 3 . :

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!

DATETIME, 1900-01-01. SQL Server 1 = 1 ( ). 1 (.. 0,5). , SQL Server 0 (1900-01-01), .

AVG DATETIME, DATETIME , , .

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

, DATETIME VARCHAR 13 :

SELECT CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME), 114) + '0' FROM #TableWithTimeValues

Answer 2

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

datetime. (1/1/1900 ). AVG() .

Answer 3

Cadaeic, , , , ....

, . , AVG TAT , TAT

SELECT 
-- calculates overall TAT for ALL Approvals for specified period of time
-- depending on parameters of query
CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(Tat_mins, 12) as datetime) AS FLOAT)) AS DATETIME), 108) + '0'

from 
(
-- tat is for individual approvals 
SELECT 
dbo.credit_application.decision_status,
dbo.credit_application.application_id,
cast(dbo.credit_application.data_entry_complete as date) as'Data Entry Date',
cast(dbo.credit_application.decision_date as DATE) as 'Decision Date',
avg(datediff(minute, dbo.credit_application.data_entry_complete, dbo.credit_application.decision_date)) as 'TAT Minutes',
convert (char(5), DateAdd(minute, Datediff(minute,dbo.credit_application.data_entry_complete, dbo.credit_application.decision_date),'00:00:00'),108) as 'TAT_Mins'
FROM  dbo.credit_application
where Decision_status not in ('P','N')

group by dbo.credit_application.decision_status,
dbo.credit_application.data_entry_complete,
dbo.credit_application.decision_date
--dbo.credit_application.application_id
)bb

Answer 4

, datetime?

, GROUP BY (Hour?) Count (*)?

Answer 5

SQL Server datetime 2 4- , datetime 8 . - , - .

datetime , "" datetime, . convert (int, getdate()). .

SQL Server 2008 ? .

, .

Answer 6

(01/01/1900), , .