I have a requirement
select (18.00-1.45) =16.55
but i want is 16.15 hrs
how to do this pls help me out..!!!
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
pradeep kumarPosted Feb 14, 2014, 1:50 AM
check my solution i've found:
select CAST((CAST(FLOOR(18.00) * 60 + FLOOR(100 * 18.00) % 100 AS INT)-CAST(FLOOR(1.45) * 60 + FLOOR(100 * 1.45) % 100 AS INT))/60 +
(CAST(FLOOR(18.00) * 60 + FLOOR(100 * 18.00) % 100 AS INT)-CAST(FLOOR(1.45) * 60 + FLOOR(100 * 1.45) % 100 AS INT))%60 *0.01 AS DECIMAL(5,2))
Jignesh TrivediPosted Feb 12, 2014, 11:06 PM
hi,
I think, we cannot achieve this in one line SQL
try
Declare @diff as float
declare @hh as int
declare @mm as int
select @diff =(DateDiff(mi,Cast('1:45' as time),Cast('18:00' as time)))/60.00
select @hh = cast(@diff as int)
select @mm = (@diff - @hh)*60
select Cast(@hh as varchar(3)) + ':' + cast(@mm as varchar(3))
hope this will help you.