Click here to Skip to main content
14,300,942 members
Rate this:
Please Sign up or sign in to vote.
See more:
Dear Sir,
I have written one SQL Queries in below. i given input as 6.78. i want to display output as 7.18(means 7 hours and 18 mins). This below Queries is giving wrong answer(6.96)



declare @eff table (id int identity(1,1), data decimal(18,2))
--insert into  @eff('6.78')

insert @eff(data) values (6.78)  --,(15.70)
select * from @eff

declare @eff2 table (id int identity(1,1),data decimal(18,2))
declare @temp2 decimal (18,2)

declare @i int
set @i=1
while((select count (data) from @eff) >=@i)
begin

if((select PARSENAME(data,1) from @eff where id = @i ) > 59 )

begin
set @temp2 = ((select data - (0.60)+convert(decimal(18,2),'.'+(convert(varchar(20),PARSENAME( data,1)))) from @eff where id = @i))
print @temp2
insert @eff2 (data) values (@temp2)
print @temp2
end


set @i=@i+1
end
select * from @eff2
Posted
Comments
Amir Mahfoozi 9-Dec-13 4:57am
   
declare @s varchar(50) = '6.78'
select case when charindex('.', @s)>0 then
cast((substring(@s,1 , charindex('.', @s)-1) +substring(@s, charindex('.', @s) +1 ,len(@s) ) / 60) as varchar(50)) +
'.'+cast((substring(@s, charindex('.', @s) +1 ,len(@s) ) % 60) as varchar(50))
else
@s
end
kamalsekhar 10-Dec-13 6:10am
   
Thanks alot Amir. now its working fine
Amir Mahfoozi 10-Dec-13 6:17am
   
You're welcome Kamal.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

If you want to work in hours and minutes, then first convert it to a total minutes (integer part of the decimal value times 60 plus fractional part times 100) and then work it back again: the integer part of the decimal is the integer number of minutes divided by 60, the fractional part is the modulus 60 value converted to a decimal, and divided by 100.
   
Comments
Maciej Los 9-Dec-13 6:56am
   
+5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100