## 7 replies

Last post Jul 22, 2015 12:59 AM by Tarun Dewangan

• Tarun Dewang...

Member

107 Points

78 Posts

### Find average time

Jul 13, 2015 02:11 AM|Tarun Dewangan|LINK

Hi everyone....

Can we store negative and positive time values in sql server database column and calculate average of it.

For that I'm using column type as varchar and store +ve and -ve time values from my application but how to calculate average of those values. From sever side query it gives an error while calculating average.

plz help me...

• mostafasydne...

Contributor

4407 Points

1266 Posts

### Re: Find average time

Hi Tarun,

#### Tarun Dewangan

I'm using column type as varchar and store +ve and -ve time values from my application

You will not be able to do the operation for varchar field for the average

You need to convert them to numeric values to get the average.

Hope this will help

Best Regards,
Mostafa

If this post helps you to resolve your problem, don't forget to "Mark as Answer"
• Nan Yu

All-Star

18165 Points

3536 Posts

Microsoft

### Re: Find average time

Jul 13, 2015 10:19 PM|Nan Yu|LINK

Hi tarun ,

You could firstly convert varchar type to numeric , and then you could calculate average of it . Code below is for your reference:

DECLARE @T2 table
(
countInfo  varchar
);
insert into @T2 (countInfo) values ('2')
insert into @T2 (countInfo) values ('6')
select  AVG(CASE WHEN ISNUMERIC(countInfo) = 1
THEN CAST(countInfo AS numeric)
END
) from @T2

Best Regards,

Nan Yu

MSDN Community Support
• Tarun Dewang...

Member

107 Points

78 Posts

### Re: Find average time

Jul 15, 2015 03:21 AM|Tarun Dewangan|LINK

hi mostafa,

I am agree with your reply and doing so, but it failed due to containing negative time value on the column (varchar type).

my query statement is below:

SELECT CONVERT(VARCHAR(255), CAST(AVG(CAST(CAST(ExTime AS DATETIME) AS DECIMAL(10,5))) AS DATETIME), 108) FROM tblTimings where MONTH(CONVERT(datetime, inDate, 105)) = MONTH(GetDate()) AND YEAR(CONVERT(datetime, inDate, 105)) = YEAR(GetDate())

is showing error i.e.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

but this query is works fine when column does not contain any negative value.

can help to convert in proper way,

thanks

• aspsak

Member

594 Points

385 Posts

### Re: Find average time

Help to each other
• Tarun Dewang...

Member

107 Points

78 Posts

### Re: Find average time

Jul 20, 2015 01:08 AM|Tarun Dewangan|LINK

Hi experts,

When I'm using cast, the calculation of average works for only positive values of time in database column at server side but my question is still same, i.e. how to find average when we are having Negative values on the table's column like below:

ExtTime

----------

00:45:00

-00:26:00

00:19:00

00:20:00

00:06:00

----------

AVG:  ?

• Nan Yu

All-Star

18165 Points

3536 Posts

Microsoft

### Re: Find average time

Jul 21, 2015 09:33 PM|Nan Yu|LINK

Hi tarun ,

#### Tarun Dewangan

how to find average when we are having Negative values on the table's column like below

You could firstly use CHARINDEX method to get "-" mark , and then you could cast varchar type to dateTime and calculate the average time,Code below is for your reference:

DECLARE @TABLE AS TABLE
(
ExtTime VARCHAR(MAX)
)
INSERT @TABLE
SELECT '00:45:00'
UNION ALL
SELECT '-00:26:00'
UNION ALL
SELECT '00:19:00'
UNION ALL
SELECT '00:20:00'
UNION ALL
SELECT '00:06:00'

;with CTE1 AS
(
SELECT
DATEDIFF(MINUTE,CONVERT(DATE,D.ExtTime),D.ExtTime) AS DIFFINMINUTE,
N.Multiplier
FROM
@TABLE T
CROSS APPLY
(
SELECT CASE WHEN CHARINDEX('-',T.ExtTime) > 0 THEN -1 ELSE 1 END Multiplier
) N
CROSS APPLY
(
SELECT CASE WHEN N.Multiplier = 1 THEN CAST(T.ExtTime AS DATETIME) ELSE CAST(STUFF(T.ExtTime,1,1,'') AS datetime) END ExtTime
) D

)
SELECT AVG(CAST(DIFFINMINUTE * Multiplier AS FLOAT)) AS AVARAGE FROM CTE1

Best  Regards,

Nan Yu

MSDN Community Support
• Tarun Dewang...

Member

107 Points

78 Posts

### Re: Find average time

Jul 22, 2015 12:59 AM|Tarun Dewangan|LINK

Hi Nan Yu,

Many Thanks for your answer. It works great. and finally I got my result what I expected.

I want one more answer for,    How cross apply and multiplier works?

I'm a front end developer and please don't take personally.

once again thanks...