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 |
|
phrankbooth
Posting Yak Master
162 Posts |
 Posted - 2008-06-17 : 19:51:10
|
| Hello,I have this column of time values:00:00:03.359267500:00:0000:00:03.859251500:00:03.687382000:00:03.843627000:00:03.3436430It is in varchar format. I tried converting it to datetime but get an invalid format error message.How can I sum up these values and average them out?Thanks!--PhB |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 19:57:30
|
You can't sum or avg datetime data:quote:
Operand data type datetime is invalid for sum operator.
Here's one way to convert to datetime:DECLARE @t table (TimeColumn varchar(16))INSERT INTO @t VALUES ('00:00:03.3592675')INSERT INTO @t VALUES ('00:00:00')INSERT INTO @t VALUES ('00:00:03.8592515')INSERT INTO @t VALUES ('00:00:03.6873820')INSERT INTO @t VALUES ('00:00:03.8436270')INSERT INTO @t VALUES ('00:00:03.3436430')SELECT CONVERT(datetime, '01/01/1900 ' + SUBSTRING(TimeColumn, 1, 12)) FROM @tTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
Math over datetime in varchar format??