Last post Jul 22, 2015 12:59 AM by Tarun Dewangan
Jul 13, 2015 02:11 AM|Tarun Dewangan|LINK
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...
thanks in advanced.
Jul 13, 2015 02:17 AM|mostafasydney|LINK
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
Jul 13, 2015 10:19 PM|Nan Yu|LINK
Hi tarun ,
Thanks for your post .
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
insert into @T2 (countInfo) values ('2')
insert into @T2 (countInfo) values ('6')
select AVG(CASE WHEN ISNUMERIC(countInfo) = 1
THEN CAST(countInfo AS numeric)
) from @T2
If you want to calculate time info ,please click
here for more information .
Jul 15, 2015 03:21 AM|Tarun Dewangan|LINK
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,
Jul 15, 2015 04:52 AM|aspsak|LINK
Jul 20, 2015 01:08 AM|Tarun Dewangan|LINK
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:
Jul 21, 2015 09:33 PM|Nan Yu|LINK
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
;with CTE1 AS
DATEDIFF(MINUTE,CONVERT(DATE,D.ExtTime),D.ExtTime) AS DIFFINMINUTE,
SELECT CASE WHEN CHARINDEX('-',T.ExtTime) > 0 THEN -1 ELSE 1 END Multiplier
SELECT CASE WHEN N.Multiplier = 1 THEN CAST(T.ExtTime AS DATETIME) ELSE CAST(STUFF(T.ExtTime,1,1,'') AS datetime) END ExtTime
SELECT AVG(CAST(DIFFINMINUTE * Multiplier AS FLOAT)) AS AVARAGE FROM CTE1
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...