14,300,942 members
Rate this:
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
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.

Rate this:

## 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.
Maciej Los 9-Dec-13 6:56am

+5!