Convert Numbers

Hi there, I'm using a SQL 2000 and I don't know how to convert (or calculate)some data as described below:

Examples
746585 means 7 hours, 46 minutes and 58.5 seconds
038335 means 38 minutes and 33.5 seconds
005215 means 5 minutes and 21.5 seconds
000455 means 45.5 seconds
000070 means 7 seconds
000065 means 6.5 seconds

How do I convert:
746585 in 466.975 minutes
038335 in 38.558 minutes
005215 in 5.358 minutes
000455 in 0.758 minutes
000070 in 0.166 minutes
000065 in 0.1083 minutes

Could anyone help me?

Thanks in advance,

AndreOkay, you have a string with numbers of the following meaning:
hmmssc

To convert it into a datetime value:

declare @.s as char(6)
set @.s='746585'

select convert(datetime,'0'+substring(@.s,1,1)+':'+
substring(@.s,2,2)+':'+substring(@.s,4,2)+'.'+
substring(@.s,6,1), 14)
go

To compute the minutes:

select cast(substring(@.s,1,1) as int)*60+
cast(substring(@.s,2,2) as int)+ cast(substring(@.s,4,3) as dec(10,5))/600

Cheers! :D|||Thank you for your reply DoktorBlue you hit almost there!!! Sorry, I just started with SQL Server...

I tryed that string:
declare @.s as char(6)
set @.s='746585'

select convert(datetime,'0'+substring(@.s,1,1)+':'+
substring(@.s,2,2)+':'+substring(@.s,4,2)+'.'+
substring(@.s,6,1), 14)
go

select cast(substring(@.s,1,1) as int)*60+
cast(substring(@.s,2,2) as int)+ cast(substring(@.s,4,3) as dec(10,5))/600

Then I got the result:
1900-01-01 07:46:58.500

The time 07:46:58.500 is very correct but it should mean a period of time, not a date.

There is a file available at http://www.aga.cc/download/sample.txt (the original one has over than 200 thousand records), so I need to calculate the total of each access#'s time and cost, something like this:

select access#, sum (time) as TIME, sum (cost) AS PRICE, sum (PRICE) / sum (TIME) as MONEY from sample group by access#.

Thank you again for your cooperation.

Andre Mori|||Originally posted by mori
select access#, sum (time) as TIME, sum (cost) AS PRICE, sum (PRICE) / sum (TIME) as MONEY from sample group by access#.


Hi Andre,

You want to have sum(time). In minutes? Use the second transformation.

You want to display your time as a string? Consider to write something like

select substring(@.s,1,1) + ' hours, ' +
substring(@.s,2,2) + ' minutes, ' + substring(@.s,4,2) +
'.' + substring(@.s,6,1) + ' seconds'|||Great DoktorBlue, 100530 converted into 60.883333333, worked fine!!! Thank you very much.

I have another awful question, how do I set @.s='the_collumn_i_want_to_convert'?

Once again, thanks.|||I used a variable to show the principle. You have to replace @.s by your field name.|||Hi DoktorBlue, please don't get angry OK?

I tryed:

declare @.time as char(6)
set @.Time = Time

select cast(substring(@.Time,1,1) as int)*60+cast(substring(@.Time,2,2) as int)+ cast(substring(@.Time,4,3) as dec(10,5))/600

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

declare @.s as char(6)
set @.s=TIME

select cast(substring(@.s,1,1) as int)*60+cast(substring(@.s,2,2) as int)+ cast(substring(@.s,4,3) as dec(10,5))/600

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

I wasn't able to make it work.....

Thanks...|||No, no, no. I assume that you have a table T with a field TIME. All you have to do is to say

SELECT TIME, cast(substring(Time,1,1) as int)*60+cast(substring(Time,2,2) as int)+ cast(substring(Time,4,3) as dec(10,5))/600
FROM T|||Hi DoktorBlue, I know you are tired of me, right? :>(

Tryed:
SELECT TIME, cast(substring(Time,1,1) as int)*60+cast(substring(Time,2,2) as int)+ cast(substring(Time,4,3) as dec(10,5))/600
FROM sample2

Returned:
Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

sample2 table has the fields:
# VARCHAR 255
Access# VARCHAR 255
Time INT
Cost INT

Do I need to declare?

Thanks again.

Thanks.|||Hi Andre,

Slowly, but we getting somewhere ....

Your error message indicates, that your TIME field isn't a CHAR, but an INT. So, replace all occurences of

substring(Time,

by

substring(right('000000' + cast(TIME as VARCHAR(6)), 6),|||Hi there, I got this code from another colleague that worked fine:

SELECT access#,
CAST(SUM(((CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-2, 3) AS DECIMAL)/10)/60) +
CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-4, 2) AS INT) +
(CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-6, 2) AS INT)*60)) AS DECIMAL(8,2)) AS TIME,
SUM(cost) AS PRICE,
CAST((SUM(cost) / SUM(((CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-2, 3) AS DECIMAL)/10)/60) +
CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-4, 2) AS INT) +
(CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-6, 2) AS INT)*60))) AS DECIMAL(8,2)) AS MONEY
FROM sample
GROUP BY access#

Thank you all!

Andre Mori