Convert Number, very difficult!!!

1. Junior Member
Join Date
Dec 1969
Posts
4

Convert Number, very difficult!!!

Hi there, I&#039;m using a SQL 2000 and I don&#039;t know how to convert (or calculate)some data as described below:<BR><BR>Examples<BR>746585 means 7 hours, 46 minutes and 58.5 seconds<BR>038335 means 38 minutes and 33.5 seconds<BR>005215 means 5 minutes and 21.5 seconds<BR>000455 means 45.5 seconds<BR>000070 means 7 seconds<BR>000065 means 6.5 seconds<BR><BR>How do I convert:<BR>746585 in 466.975 minutes<BR>038335 in 38.558 minutes<BR>005215 in 5.358 minutes<BR>000455 in 0.758 minutes<BR>000070 in 0.166 minutes<BR>000065 in 0.1083 minutes<BR><BR>Could anyone help me?<BR><BR>Thanks in advance,<BR><BR>Andre

2. Senior Member
Join Date
Dec 1969
Posts
16,931

RE: Convert Number, very difficult!!!

"000065" means "6.5" seconds. Wow. Ok.<BR><BR>In VBScript:<BR>Dim sNumber: sNumber = "000065"<BR>Dim sSeconds: sSeconds = CDbl(sNumber) / 10000<BR><BR>In SQL:<BR>SELECT (CAST(sNumber as float) / 10000) AS Seconds FROM MyTable<BR><BR>The others really are just number-crunching. To get the number of hours from "746585", you do:<BR><BR>SELECT ((CAST(sNumber as float) - (CAST(sNumber as float) MOD 10000))/10000) AS NumberHours FROM MyTable<BR><BR>It&#039;s ugly as sin - maybe you want to do this in a stored procedure or something. If you&#039;re going to do it a lot, maybe it&#039;s worth having a table with cached numbers that you pre-populate every 5 minutes with live data...?<BR>Or, actually, why not just have extra columns in your database... Use a trigger or scheduled job to populate those columns if a row changes...?<BR><BR>Craig.

3. Junior Member
Join Date
Dec 1969
Posts
4

RE: Convert Number, very difficult!!!

Hi Craig, thank you for your prompt response. Those forums can really help us a lot.<BR><BR>I tryed that code (SELECT (CAST(time as float) / 10000) AS Seconds FROM sample) and I got:<BR><BR>Seconds<BR>0.0195<BR>10.05300000000000 1<BR>0.019<BR>0.0395<BR>0.1245<BR>4.25000000000000 03E-2<BR>3.3500000000000002E-2<BR><BR>but:<BR>0.0195 should be 19.5 seconds<BR>10.053000000000001 should be 1 hour and 53 seconds (or 60.88 minutes)<BR><BR>Than I tryed the other one (SELECT ((CAST(time as float) - (CAST(time as float) MOD 10000))/10000) AS NumberHours FROM sample) but I got:<BR><BR>Server: Msg 170, Level 15, State 1, Line 1<BR>Line 1: Incorrect syntax near &#039;MOD&#039;.<BR><BR>There is a sample 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#&#039;s time and cost, something like this:<BR><BR>select access#, sum (time) as TIME, sum (cost) AS PRICE, sum (PRICE) / sum (TIME) as MONEY from sample group by access#.<BR><BR>Thank you again for your cooperation.<BR><BR>Andre Mori<BR><BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
16,931

Someone else?

Ok, I guessed that SQL Server had a "MOD" keyword. Ok...<BR><BR>Unfortunately, I&#039;ve just been left as the only one in the office, so I&#039;m manning all the phones and trying to do some work...<BR><BR>Oh, Craig. You&#039;re such an idiot.<BR><BR>All you need to do is use the string-handling functions. The first character of the string is the number of hours. Fine. Use SUBSTRING() to extract it. The next two are the number of minutes. Fine, use SUBSTRING() to extract it...<BR><BR>Yeah/no?<BR><BR>Craig.

5. Senior Member
Join Date
Dec 1969
Posts
342

RE: Someone else?

In case you were still wondering, the SQL version of "MOD" is "%". ;-)

6. Senior Member
Join Date
Dec 1969
Posts
16,931

RE: Someone else?

I thought it probably was - like C/Java/C#....<BR><BR>Suddenly realised the error of my ways, though.... :-)<BR><BR>Craig.

7. Junior Member
Join Date
Dec 1969
Posts
4

RE: Someone else?

Hi Craig, thank you again. I have no ideia where to put SUBSTRING() in the code you sent to me..... :&#062;(

8. Junior Member
Join Date
Dec 1969
Posts
4

RE: Someone else?

Hi there, I got this code from another collegue, please take a look:<BR><BR><BR>declare @s as char(6)<BR>set @s=&#039;100530&#039;<BR><BR>select cast(substring(@s,1,1) as int)*60+<BR>cast(substring(@s,2,2) as int)+ cast(substring(@s,4,3) as dec(10,5))/600<BR><BR>Returns:<BR>60.883333333<BR><BR>works fine, but, how do I replace &#039;100503&#039; by the field_name_i_want_to_convert??? I don&#039;t want to type each number I want to convert..... :&#062;(<BR><BR>I tryed<BR><BR>declare @s as char(6)<BR>set @s=&#039;field_name&#039;<BR><BR>and<BR><BR>declar e @s as char(6)<BR>set @s=field_name<BR><BR>but didn&#039;t work.<BR><BR>Many thanks for all replies.

9. Senior Member
Join Date
Dec 1969
Posts
342

RE: Someone else?

Forget setting the @s variable, just replace @s in your select statement with the name of the field.<BR><BR>ie:<BR><BR>select cast(substring(YourField,1,1) as int)*60+<BR>cast(substring(YourField,2,2) as int)+ cast(substring(YourField,4,3) as dec(10,5))/600 as Minutes<BR>from SomeTable where [some condition]<BR><BR>

Posting Permissions

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