# Convert Numbers

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

1. ### moriNew 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?

Andre
2. ### FrankKalisModerator

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. ### moriNew 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. ### bambolaNew 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. ### FrankKalisModerator

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. ### TwanNew 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. ### bambolaNew 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. ### TwanNew 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

10. ### TwanNew 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. ### bambolaNew 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. ### moriNew 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. ### bambolaNew 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. ### moriNew 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

Any thoughts?

Thanks.
15. ### bambolaNew 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. ### moriNew 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. ### TwanNew 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. ### bambolaNew 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. ### moriNew 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

Hi Mori