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

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

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.

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

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.

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

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 />

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

&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 />

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.

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.

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.

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.

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?

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 />

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.

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

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