October 17, 2007 at 12:28 pm
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
0.525120
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?
ps, I just realized I titled this wrong, it's not an integer to datetime, should be numeric or float.
October 17, 2007 at 1:03 pm
Maybe you'll be better off selecting timeparts from your datetime and averaging those numbers.
Have a look at http://www.sqlservercentral.com/articles/Advanced+Querying/workingwithdatetime/1634/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 17, 2007 at 1:09 pm
Here's one that should do it:
dateadd(ss,avg(convert(float(53),dateadd(dd,datediff(dd,datetimetoavg,0),datetimetoavg)))*24*60*60,0)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2007 at 1:14 pm
or in seconds
SELECT substring(convert(char(23),dateadd(ss,A.myavg,DATEADD(dd, DATEDIFF(dd,0,@x), 0)),121),11,8) as myavg
from (
SELECT avg(datediff(ss, DATEADD(dd, DATEDIFF(dd,0,@x), 0), @x)) as myavg
from whatever
)
A
for the moment I have no SSMS, so this tsql is untested ![/b]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 17, 2007 at 3:57 pm
I think this does what you want.
select
TIME_NUMERIC,
-- Convert from number back to datetime on day 1900-01-01
BACK_TO_TIME = dateadd(ms,TIME_NUMERIC*3600000.0000,0),
DT
from
(
select
TIME_NUMERIC =
-- Extracts time only from date and converts it to numeric
convert(numeric(10,8),round(datediff(ms,dateadd(dd,datediff(dd,0,DT),0),DT)/3600000.0000,8)),
DT
from
(
-- Test data
select DT =getdate()union all
select DT = '20071001 14:23:45:667' union all
select DT = '20071130 23:59:59:997'
)a ) aa
TIME_NUMERIC BACK_TO_TIME DT
------------ ---------------------- -----------------------
17.89564889 1900-01-01 17:53:44.337 2007-10-17 17:53:44.337
14.39601833 1900-01-01 14:23:45.667 2007-10-01 14:23:45.667
23.99999889 1900-01-01 23:59:59.997 2007-11-30 23:59:59.997
(3 row(s) affected)
October 18, 2007 at 9:19 am
Thanks everyone for your replies,
Matt your reply was the one I went with that ended up working great.
ALZBA I wasn't sure how to apply your solution, because I'm comparing one column, I wasn't sure what arguments to supply to the datediff...
MVJ your solution was the most intriguing and worked when comparing all values in a datetime column but I didn't specify in my original post that I was going to go on and break that column up by using a case based on criteria in another column. So if I want to pluck records out of a table based on values in another how could I apply your solution?
ie,
select
case when column1 = 'a' then
average datetime column2
end
case when column1 = 'b' then
average datetime column2
end
from table1
October 18, 2007 at 11:43 am
Marcus Farrugia (10/18/2007)
Thanks everyone for your replies,Matt your reply was the one I went with that ended up working great.
ALZBA I wasn't sure how to apply your solution, because I'm comparing one column, I wasn't sure what arguments to supply to the datediff...
MVJ your solution was the most intriguing and worked when comparing all values in a datetime column but I didn't specify in my original post that I was going to go on and break that column up by using a case based on criteria in another column. So if I want to pluck records out of a table based on values in another how could I apply your solution?
ie,
select
case when column1 = 'a' then
average datetime column2
end
case when column1 = 'b' then
average datetime column2
end
from table1
I don't understand what you are asking.
What do you mean by "break that column up"?
Please explain in detail what you want to do.
October 18, 2007 at 12:11 pm
MVJ - In you example you supply 3 values DT = getdate(), DT = '20071001 14:23:45:667' , DT = '20071130 23:59:59:997'
and the average time resulting in the same three values that were originally given so it's an average of one value.
So lets say I have two columns in my table
Column 1 has the following values:
a, a, b, b, c, c, d, d
and column 2 has these timedate values:
'20071130 23:59:59:997'
'20071130 14:23:45:667'
'20071130 12:44:59:997'
'20071130 00:39:59:997'
'20071130 08:18:59:997'
'20071130 17:55:59:997'
'20071130 10:28:59:997'
'20071130 22:08:59:997'
So now I want to get the average time where column 1 has a value of 'a' in this case it would be the average of these two values:
'20071130 23:59:59:997',
'20071130 14:23:45:667'
So how would I apply that your solution to return the average time when column 1 in ('a', 'b', 'c', 'd')?
Hope this make more sense.
October 18, 2007 at 4:42 pm
select
Avg_time =
avg(convert(numeric(10,8),round(datediff(ms,dateadd(dd,datediff(dd,0,a.DT),0),a.DT)/3600000.0000,8)))
from
MyTable a
where
Col_a in ('a', 'b', 'c', 'd')
October 19, 2007 at 12:23 am
Marcus Farrugia (10/18/2007)
... ALZBA I wasn't sure how to apply your solution, because I'm comparing one column, I wasn't sure what arguments to supply to the datediff...case when column1 = 'a' then
average datetime column2
end
case when column1 = 'b' then
average datetime column2
end
from table1
Indeed sometimes you need to play around to find out wich solution serves you best 😉
....
Here are some alternatives :
DECLARE @Tmp TABLE (Col1 CHAR(1) NOT NULL, Dt DATETIME NOT NULL)
SET NOCOUNT ON
INSERT INTO @Tmp values('a', '20071130 23:59:59:997')
INSERT INTO @Tmp values('a', '20071130 14:23:45:667')
INSERT INTO @Tmp values('b', '20071130 12:44:59:997')
INSERT INTO @Tmp values('c', '20071130 00:39:59:997')
INSERT INTO @Tmp values('b', '20071130 08:18:59:997')
INSERT INTO @Tmp values('b', '20071130 17:55:59:997')
INSERT INTO @Tmp values('d', '20071130 10:28:59:997')
INSERT INTO @Tmp values('d', '20071130 22:08:59:997')
SET NOCOUNT OFF
SELECT Col1, avg(datediff(ss, DATEADD(dd, DATEDIFF(dd,0,Dt), 0), Dt)) as Avg_ss , COUNT(*) AS NumRows
from @Tmp
GROUP BY Col1
ORDER BY Col1 ;
SELECT A.Col1
, SUBSTRING (CONVERT(char(23),dateadd(ss,A.Avg_ss,DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0 )),121),12,8) as TheAvgTime
, A.Avg_SS
, A.NumRows
from (
SELECT Col1, avg(datediff(ss, DATEADD(dd, DATEDIFF(dd,0,Dt), 0), Dt)) as Avg_ss , COUNT(*) AS NumRows
from @Tmp
GROUP BY Col1 ) A
ORDER BY A.Col1 ;
-- using a CTE
; WITH cteAvgSs (GroupedCol1 , Avg_Ss, NumRows ) AS
( SELECT Col1, avg(datediff(ss, DATEADD(dd, DATEDIFF(dd,0,Dt), 0), Dt)) , COUNT(*)
from @Tmp
GROUP BY Col1 )
SELECT GroupedCol1
, SUBSTRING (CONVERT(char(23),dateadd(ss,Avg_ss,DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0 )),121),12,8) as TheAvgTime
, Avg_SS
, NumRows
from cteAvgSs
ORDER BY GroupedCol1 ;
resulting in:
Col1 Avg_ss NumRows
---- ----------- -----------
a 69112 2
b 46799 3
c 2399 1
d 58739 2
(4 row(s) affected)
Col1 TheAvgTime Avg_SS NumRows
---- ---------- ----------- -----------
a 19:11:52 69112 2
b 12:59:59 46799 3
c 00:39:59 2399 1
d 16:18:59 58739 2
(4 row(s) affected)
GroupedCol1 TheAvgTime Avg_SS NumRows
----------- ---------- ----------- -----------
a 19:11:52 69112 2
b 12:59:59 46799 3
c 00:39:59 2399 1
d 16:18:59 58739 2
(4 row(s) affected)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 19, 2007 at 12:32 am
plz mak uze of the followin link.
http://www.sql-server-helper.com/functions/get-date-only.aspx
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply