Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Irfan.abdullah
Starting Member
9 Posts |
 Posted - 2012-09-05 : 19:44:24
|
| Hi,I have a date field that is formated to show data in hrs.minutes.secondHow do I calculate average on this pleaseThanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-05 : 20:43:44
|
| [code]SELECT CONVERT(varchar(8), DATEADD(ss,AVG(datediff(ss,0,datefield)*1.0),0),108)FROM table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Irfan.abdullah
Starting Member
9 Posts |
Posted - 2012-09-06 : 19:45:33
|
| Thanks for reply can you please explain in more detail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-08 : 17:10:57
|
| try it out on sample dataset and you will understand how it returns you averagedatediff will return seconds elapsed since base date ie time valuetaking average over it gives average timeagain adding it to 0 will make it date value which when converted to string using 108 gives you result in hh:mm:ss formatseehttp://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Date format