SQL Server Performance

Convert Numbers

Discussion in 'General Developer Questions' started by mori, Sep 4, 2003.

  1. mori New Member

    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. FrankKalis Moderator

    I guess you have no chance to change the underlying table design into say three separate columns for hours, minutes and seconds?

    Cheers,
    Frank
  3. mori New Member

    Thank you for helping me Frank. How do I make it work? How can I make this separation? To be honest I just started with SQL Server...

    Regatrds,
    Andre
  4. bambola New Member

    In this case, and since there is no time only datatype, why not go with one field that holds number of milliseconds
    or seconds past midnight?

    Bambola.
  5. FrankKalis Moderator

    So when you are able to change the table structure, maybe you should give some information about what you are trying to achieve, what this data will be used for...
    Bambolas' suggestion is certainly worth thinking over.

    Cheers,
    Frank
  6. Twan New Member

    Here is a script that may help...

    declare @table_orig table(
    time_text varchar(20) )

    insert into @table_orig values ( '59546585' )
    insert into @table_orig values ( '746585' )
    insert into @table_orig values ( '038335' )
    insert into @table_orig values ( '005215' )
    insert into @table_orig values ( '000455' )
    insert into @table_orig values ( '000070' )
    insert into @table_orig values ( '000065' )

    select * from @table_orig

    select hours = convert( int, substring( time_text, 1, datalength( time_text ) - 5 ) ),
    minutes = convert( int, substring( time_text, datalength( time_text ) - 4, 2 ) ),
    seconds = convert( decimal(4,1), substring( time_text, datalength( time_text ) - 2, 3 ) ) / 10,
    total_milliseconds = ( ( convert( int, substring( time_text, 1, datalength( time_text ) - 5 ) ) * 36000 )
    + ( convert( int, substring( time_text, datalength( time_text ) - 4, 2 ) ) * 600 )
    + ( convert( int, substring( time_text, datalength( time_text ) - 2, 3 ) ) ) )
    * 100
    from@table_orig



    as Bambola suggests storing the number of millseconds may be easier, for the int datatype this equates to about 595 hours or so

    Cheers
    Twan
  7. bambola New Member

    With a little modification to get the correct number of milliseconds <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />select hours = convert( int, substring( time_text, 1, datalength( time_text ) - 5 ) ),<br />minutes = convert( int, substring( time_text, datalength( time_text ) - 4, 2 ) ),<br />seconds = convert( decimal(4,1), substring( time_text, datalength( time_text ) - 2, 3 ) ) / 10,<br />total_milliseconds = ( ( convert( int, substring( time_text, 1, datalength( time_text ) - 5 ) ) * 36000 )<br />+ ( convert( int, substring( time_text, datalength( time_text ) - 4, 2 ) ) * 600 )<br />+ ( convert( int, substring( time_text, datalength( time_text ) - 2, 3 ) ) ) )<br /><b>* 100/1000.*300</b><br />from @table_orig<br /><br />Bambola.
  8. Twan New Member

    Bambola,

    uhm I don't get it... the last value is 6.5 seconds. I'm pretty sure that's 6500 milliseconds and not 1950...

    Why divide by 1000 and multiple by 300?

    Cheers
    Twan
  9. bambola New Member

    I'll explain. <br />6.5 seconds means 6 seconds and 500 miliseconds. so far I suppose we agree. <br />datetime is held as 2 ints, one for number of days past 1900-01-01 and one for number of clock ticks past midnight. There are 300 clock ticks in a second.<br /><br />For this example, I will ignore the date part and refer to the time part only. the int that represents the date part would be 0 (meaning 1900-01-01).<br /><br />I will add to 6 seconds and 500 milliseconds to 0 (1900-01-01)<br /><br />select dateadd(ms, 500, dateadd(second, 6, 0))<br /><br />@d now contains '1900-01-01 00:00:06.500'<br /><br />now I will try to convert all to milliseconds. <pre><br />declare @d datetime<br />select @d = dateadd(ms, 500, dateadd(second, 6, 0))<br /><br />SELECT<br /> (<br /> DATEPART(hour, @d) * 60 * 60 -- hours * 60 minutes * 60 seconds<br /> + DATEPART(minute, @d) * 60 -- minutes * 60 seconds<br /> + DATEPART(second, @d) -- seconds<br /> ) * 300 -- clock ticks in 1 seconds<br /> + CAST(DATEPART(ms,@d)/1000.*300 as int) -- add the millisecond part<br /><br />in this case<br /><br />SELECT<br /> (<br /> 0 * 60 * 60 -- 0 hours<br /> + 0 * 60 -- 0 minutes<br /> + 6 -- 6 seconds<br /> ) * 300 -- multiple all for 300 clock ticks <br /> + 500/1000. * 300 -- add the millisecond part <br /></pre><br />Both return 1950 milliseconds. (corrected.)<br /><br />To confirm what I am saying, you can read the int that represent the time part and see it holds the same value<br /><br />select cast( substring(convert(binary(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, @d), 5, <img src='/community/emoticons/emotion-11.gif' alt='8)' /> as int)<br /><br />or convert milliseconds back to datetime<br /><br />select dateadd(ms, 6500, 0), dateadd(ms, 1950, 0)<br />dateadd(ms, 6500, 0) will return '1900-01-01 00:00:06.500' <br />dateadd(ms, 1950, 0) will return '1900-01-01 00:00:01.950'<br /><br />Bambola.<br /><br /><font face="Book Antiqua"><font size="2">If we are to achieve results never before accomplished, we must expect to employ methods never before attempted. - Francis Bacon</font id="size2"></font id="Book Antiqua"><br />
  10. Twan New Member

    both return 1950 on my machine... <br /><br /><br />the format that the data is held in at the moment is a char field, not a datetime... So my code shows the conversion from char to ms, rather than datetime to ms...<br /><br />i.e convert '000065' to milliseconds<br /><br /><br /><br />so <br /><br />select cast( substring(convert(binary(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, @d), 5, <img src='/community/emoticons/emotion-11.gif' alt='8)' /> as int) / 300.0 * 1000 <br /><br />also reutns the correct number of milliseconds, but that's a different problem...?<br /><br />Twan
  11. bambola New Member

    &gt; both return 1950 on my machine... <br />Your machine is correct. I meant both return 1950 (and not 6500 as written before). <br />And my last line confirms that the value in millisecons is 6500 as you said.<br /><br />What I had in mind (and got a bit confused <img src='/community/emoticons/emotion-1.gif' alt=':)' /> was to hold the number of clock ticks (1950) and not the milliseconds as this is how SQL Server holds it. in this case converting it to datetime (and be able to use the date functions) would simply mean reading it in a different way without the need to convert it or add milliseconds to it. <br /><br />declare @d1 datetime<br />select @d1 = cast(1950 as binary(<img src='/community/emoticons/emotion-11.gif' alt='8)' />)<br />select @d1<br /><br />And select cast( substring(convert(binary(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, @d), 5, <img src='/community/emoticons/emotion-11.gif' alt='8)' /> as int) / 300.0 * 1000 returns 650.000000 and not the correct value (clock ticks or milliseconds). <br /><br />Hope I made myself clear this time <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Bambola.<br /><br /><font face="Book Antiqua"><font size="2">If we are to achieve results never before accomplished, we must expect to employ methods never before attempted. - Francis Bacon</font id="size2"></font id="Book Antiqua"><br />
  12. mori New Member

    Hi there, I got this code from another collegue, please take a look:


    declare @s as char(6)
    set @s='100530'

    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

    Returns:
    60.883333333

    works fine, but, how do I replace '100503' by the field_name_i_want_to_convert??? I don't want to type each number I want to convert..... :>(

    I tryed

    declare @s as char(6)
    set @s='field_name'

    and

    declare @s as char(6)
    set @s=field_name

    but didn't work.

    Many thanks for all replies.
  13. bambola New Member

    to your question you just replace @s with field_name

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

    Though I cannot figure out what is it supposed to be doing and what 60.883333333 stands for...

    Bambola.
  14. mori New Member

    Hi Bambola,

    Sorry, what I meant is:

    100530 means 60.833333333 minutes because:
    100530 = 1 hour and 53 seconds

    I tryed that but didn't work

    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

    returns:
    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'time'.
    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'time'.
    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'time'.

    My field name is time, I also tryed

    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
    from sample2 (my table_name available at www.aga.cc/download/sample.txt) but didn't work either.

    Any thoughts?

    Thanks.
  15. bambola New Member

    Are you sure you have a column name time on your table?
    also wrap time with [] as it is a reserverd word.

    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

    Bambola.
  16. mori New Member

    Tryed:

    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

    returns:
    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'time'.
    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'time'.
    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'time'.

    or

    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
    from sample

    Server: Msg 8114, Level 16, State 5, Line 1
    Error converting data type varchar to numeric.

    My field's name:
    # Access# Time Cost

    Do I need to declare?
  17. Twan New Member

    Can you confirm that your field time is always 6 numeric digits...?<br /><br />Bambola:<br />And select cast( substring(convert(binary(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, @d), 5, <img src='/community/emoticons/emotion-11.gif' alt='8)' /> as int) / 300.0 * 1000 returns 650.000000 and not the correct value (clock ticks or milliseconds). <br /><br />It does return 6500.00000 here... assuming @d is 1950 ticks...<br /><br />
  18. bambola New Member

    You are right again, Twan! I still have it open on QA and it says 300.0 * 100 :-P That's what happens when you try to work, answer posts and make a coffee all at the same time. The coffee came out good by the way.... -<img src='/community/emoticons/emotion-5.gif' alt=';)' /> And I still think that storing the "milliseconds" as int and reading it as datetime was a nice idea.<br /><br />Bambola.
  19. mori New Member

    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#

    2329543 means 23 hours, 29 minutes and 54.3 seconds.

    Thank you all!

    Andre Mori
  20. Twan New Member

    Hi Mori

    The answer you put here gives you the answer in minutes...

    also if you're casting one of them as decimal then you might as well cast all of the them as decimal to avoid SQLServer casting first to int and then to decimal

    finally the time column returned by the statement above is set to decimal(8,2) this will only have 2 digits to the right of the deimal place. Since you are dividing by an integer by 600, you are losing 0.6 seconds of accuracy... Not sure if that is significant for you...?

    If you do have the oportunity to store the data in a more useful way then either storing it as an int number of ms or as a datetime (per Bambola) then that would be much more convenient...

    Cheers
    Twan

Share This Page