Convertion of seconds 1

[rute67]

I am trying to create some system reports. I recieve the data into SQL thru a DTS package and it is received as a varchar(255). The only problem with that is that the information I am having problems with are seconds. I am then converting to numeric(25) in the query. now the data that I am working with, Which are supposted to be seconds and come in as varchar, are now in what I think is the right format.

Now to the problem. I am trying to convert these seconds to minutes to run reports of them, but when I try to divid by 60 to give me minutes then I get for some number 16:88 minutes. Of course that is incorrect. So my question is at the end of all of this junk up above is:

How can I convert numbers that come in as varchar to seconds an get minutes from that?

Sorry for all the mess up above and thanks for all the help.

 

[SQLSister]

give us some examples of the data that you have and the resluts you want. Particularly the data that results inthe wrong answer.

 

[SQLBill]

And if possible, posting your conversion script might help.

-SQLBill

 

[rute67]

Here is the conversion:

cast(cast(Talk_duration as numeric(10))/60 as dec(5,2)) as Talk_duration


Here is the result.

Talk_duration is in seconds = 228
results in minutes = 3.80

 

[cehepker]

Hello,

To get the seconds, use the modulo operator (%), which returns the remainder of a division problem.

declare @Talk_Duration varchar(255)
declare @Talk_Minutes smallint
DECLARE @Talk_Seconds smallint

Select @Talk_Duration = '288'
SET @Talk_Seconds = @Talk_Duration % 60
SET @Talk_Minutes = (@Talk_Duration - @Talk_Seconds)/60

SELECT @Talk_Minutes AS Minutes
SELECT @Talk_Seconds AS Seconds

It is easiest to subtract the seconds from the total and then divide to get minutes to eliminate any round problems.

Maybe this will work for you,
Carla

Documentation: A Shaft of light into a Coded world

 

[SQLSister]

And that would be a correct answer. 3.8 minutes is not the same thing 3 minutes 80 seconds. It is the mathmatical equivalent of 3 minutes 48 seconds.

 

[rute67]

I am sorry but I miss communicated myself.

I have a value for Talk_duration of 228.

I used this script to get a result:

cast(cast(Talk_duration as numeric(10))/60 as dec(5,2)) as Talk_duration

And I get this result: 3.80

I am sorry for the miscommunication.

 

[sunila7]

Hi,

Try this scrpit

declare @i as int

set @i = 228

Select convert(varchar(10),dateadd(mi,228,'1/1/1900'),114)

Sunil

 

[rute67]

that script worked but how can I get the set @i = 228 where 228 equals the field name that I am trying to use. I tried it with the field name and it gives me an error:

Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Talk_duration'.

Where I know that Talk_duration is a valid column name in my database.

 

[SQLBill]

Do you start your script with the USE command?

USE mydatabase
go
/*your script here*/


-SQLBill

 

[rute67]

Got it.....


Thanks for all of your help