Results 1 to 11 of 11

Thread: Convert Numbers

  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: 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,

    Andre

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Convert Numbers

    Okay, 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!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    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
    Attached Files Attached Files

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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'
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Sep 2003
    Posts
    6
    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.

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I used a variable to show the principle. You have to replace @s by your field name.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Sep 2003
    Posts
    6
    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...

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  9. #9
    Join Date
    Sep 2003
    Posts
    6

    :>(

    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.
    Attached Files Attached Files

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: :>(

    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),
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Sep 2003
    Posts
    6

    solved! thank you all!

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •