Error Converting data type

Hello,
I've already put one post about thos problem but i didn't put every problems
that i'm having
I need to get the difference between duration of my backups and the time
backups are queued.
The question is that the aplication use one repository in SQL Server and the
table where this information is inserted has this two fields with
[varchar](6) data type.
When i try to make this difference i've some problems like:
- select cast(substring(queuing,1,2) as int) from table
or
- select cast(substring(duration,1,2) as int) from table
Syntax error converting the varchar value '0:' to a column of data type int.
This is because(like i put at the end of this message) duration and queuing
are char and if they have less than one hour i cant treat this information
because they only have one 0(zero) instead of two(00)
Another problem that i had, were all records that had duration greater then
24:00
I need to get the difference of (duration - queuing)
based on the values that i put in the end
durat queuing
0:16 0:00
29:03 0:00
0:15 0:00
25:22 0:03
0:38 0:18
12:54 0:00
18:45 0:00
17:46 0:00
I want to thanks to Mike Epprecht and to Aleksandar Grbic, they already try
to help me with this question but i guess that i didnt send all information
and so i cant use they solutions.
Hope you can help me again.
Thanks everybody and best regards.try with replace
example
select cast(replace(substring(field,1,2),':',''
) as int)
"CC&JM" wrote:

> Hello,
> I've already put one post about thos problem but i didn't put every proble
ms
> that i'm having
> I need to get the difference between duration of my backups and the time
> backups are queued.
> The question is that the aplication use one repository in SQL Server and t
he
> table where this information is inserted has this two fields with
> [varchar](6) data type.
> When i try to make this difference i've some problems like:
> - select cast(substring(queuing,1,2) as int) from table
> or
> - select cast(substring(duration,1,2) as int) from table
> Syntax error converting the varchar value '0:' to a column of data type in
t.
> This is because(like i put at the end of this message) duration and queuin
g
> are char and if they have less than one hour i cant treat this information
> because they only have one 0(zero) instead of two(00)
> Another problem that i had, were all records that had duration greater the
n
> 24:00
> I need to get the difference of (duration - queuing)
> based on the values that i put in the end
> durat queuing
> 0:16 0:00
> 29:03 0:00
> 0:15 0:00
> 25:22 0:03
> 0:38 0:18
> 12:54 0:00
> 18:45 0:00
> 17:46 0:00
> I want to thanks to Mike Epprecht and to Aleksandar Grbic, they already tr
y
> to help me with this question but i guess that i didnt send all informatio
n
> and so i cant use they solutions.
> Hope you can help me again.
> Thanks everybody and best regards.
>|||You could do this several ways, but one that would work... Here are the
steps
Pick off the hours and minutes from each field. ( use Charindex to find the
':', then use substring or left and right functions to pull off the hours
and minutes... convert each to int, and multiply the hours * 60 and add
them together... Once you have done that for both fields simply subtract to
get the difference.
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:F744E35A-BC16-43A8-A58E-86BE53A18810@.microsoft.com...
> Hello,
> I've already put one post about thos problem but i didn't put every
> problems
> that i'm having
> I need to get the difference between duration of my backups and the time
> backups are queued.
> The question is that the aplication use one repository in SQL Server and
> the
> table where this information is inserted has this two fields with
> [varchar](6) data type.
> When i try to make this difference i've some problems like:
> - select cast(substring(queuing,1,2) as int) from table
> or
> - select cast(substring(duration,1,2) as int) from table
> Syntax error converting the varchar value '0:' to a column of data type
> int.
> This is because(like i put at the end of this message) duration and
> queuing
> are char and if they have less than one hour i cant treat this information
> because they only have one 0(zero) instead of two(00)
> Another problem that i had, were all records that had duration greater
> then
> 24:00
> I need to get the difference of (duration - queuing)
> based on the values that i put in the end
> durat queuing
> 0:16 0:00
> 29:03 0:00
> 0:15 0:00
> 25:22 0:03
> 0:38 0:18
> 12:54 0:00
> 18:45 0:00
> 17:46 0:00
> I want to thanks to Mike Epprecht and to Aleksandar Grbic, they already
> try
> to help me with this question but i guess that i didnt send all
> information
> and so i cant use they solutions.
> Hope you can help me again.
> Thanks everybody and best regards.
>