conversion error
-
I'm working on a query that I almost have working 100%. The only error I now receive is:
Syntax error converting the varchar value '00:00:00' to a column of data type int.
The column in question is a column that stores the amount of video recorded. It is of type varchar. I was trying to convert it to type bigint by doing this:
SUM(CAST(WCvideoAmount AS int)) AS 'Total Video'
But that doesn't work because the column data contains colons.
Any idea on how to fix this?
Thanks!
-
Is the 00:00:00 hours/mins/secs? and is the output int meant to be seconds?
If so, you could maybe do something like...
select SUM(datediff(s, 0, convert(datetime, WCvideoAmount , 8))) AS 'Total Video'
ie. convert the varchar to a datetime (see BOL CONVERT for description), compare it with a base (0 basically equates to '00:00:00') and output difference in seconds
Jon
-
thank you for your reply jt-75
Yes, it's hours:minutes:seconds. And I want to find the average and have the output be the same(hours:minutes:seconds).
-
erm...
select convert(varchar(8), dateadd(s, AVG(datediff(s, 0, convert(datetime, WCvideoAmount , 8))), 0), 8)
maybe
..
I think
-
jt-75...I tried both versions that you provided, yet I still get:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Now I'm not sure what the deal is here. The column that I am working on is of format '00:00:00' and is of type varchar.
It seems like it wants to work...the results start display but are quickly replaced by the error above.
At first I thought maybe some of the rows in the column are not in the '00:00:00' format, but I checked, and they are all in that exact format.
There's one thing that I think might cause the problem though...since it is a time, it has a maximum time of 23:59:59 right? If one of the numbers is over, then I imagine it would cause that error...for example, if one row was 25:00:01 it would error out with the above error if my theory holds.
-
Check data in your table first.
Use
SELECT ...
WHERE ISDATE (WCvideoAmount) = 0
It will show you all rows with values causing error.
_____________
Code for TallyGenerator -
Personally I'd suggest converting the time into seconds and storing as number of seconds. It can be converted back to hh:mm:ss for display purposes easily enough
SELECT CAST(SUBSTRING(WCVideoAmount,1,2) As INT)*3600 + CAST(SUBSTRING(WCVideoAmount,4,2) As INT)*60 + CAST(SUBSTRING(WCVideoAmount,7,2) As INT)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I absolutly agree with "GilaMonster".
Having a column with parts violates the principles of relational databases and will cause a significant number of bugs. For example: There was a Mars Lander crash due to poor selection of the underlying data type.
Another example is the JobHistory table in the msdb database where the job start datetime is in two columns: "date" is recorded as an integer and "time" is a seperate integer column. Try writing a SQL statement to find all jobs that ran in the last 24 hours.
Strongly recommend changing the physical datatype to integer and storing seconds only.
You have also included in the column name "Amount" which usually means some financial amount (dollars or euros).
The column name should be absolutely clear on the unit of measure. Instead of VideoElapsedTime use VideoElapsedSeconds.
To convert to a display format, just write a User Defined Function (not tested)
create function Seconds_to_HHMMSS
(@Seconds integer)
returns varchar(9)
as
begin
declare @DisplaySeconds integer
, @DisplayMinutes integer
, @displayHours integer
, @DisplayHHMMSS char(8)
SET @DisplayHours = @Seconds / ( 60 * 60)
SET @Seconds = @Seconds - ( @DisplayHours * ( 60 * 60) )
set @DisplayMinutes = @Seconds / 60
SET @Seconds = @Seconds - ( @DisplayMinutes * 60 )
SET @DisplayHHMMSS
= CAST( @DisplayHours as varchar(2) ) + ':'
+ CAST( @DisplayMinutes as varchar(2) ) + ':'
+ CAST( @DisplaySeconds as varchar(2) )
RETURN (@DisplayHHMMSS )
END
go
From: http://www.dbazine.com/ofinterest/oi-articles/celko30
Non-atomic Data Elements
The most common newbie error is splitting a temporal data element into (year, month, day) columns or as (year, month) columns, or (year) columns. The problem with temporal data is that, by its nature, it is not atomic; it is a continuum. A continuum has no atomic parts; it can be infinitely subdivided. Thus, the year 2005 is shorthand for the pair (“2005-01-01 00:00:00,” “2005-12-31 23:59:59.999..”) by which we live with the precision that our SQL product has for the open end on the left. It includes every point in between — every uncountably infinite one of them.
The Greeks did not have the concept of a continuum, and this lead to Zeno’s famous paradoxes.
SQL = Scarcely Qualifies as a Language
-
Another example is the JobHistory table in the msdb database where the job start datetime is in two columns: "date" is recorded as an integer and "time" is a seperate integer column. Try writing a SQL statement to find all jobs that ran in the last 24 hours.
and the run duration is also stored as an integer in the format hhmmss.
I have a query to find jobs that failed in the last 24 hours. I don't understand what it does anymore and I wrote it.
Speaking of bad designs, sysjobschedules anyone? (specifically freq_type and freq_interval)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply