DateDiff??
-
I am trying to retrive for a record 1 day and 1 hour but what i am getting back is 25 hours...is there a quick way of doing this...
Moe C -
1 day and 1 hour = 25 hours
Do you have sample data and the wished outcome?
-
This might give you a solution:
SELECT CAST(25/24 AS VARCHAR(2)) + ' Hours' + ' and ' + CAST(25%24 AS VARCHAR(2)) + ' Minutes'
Note: There is one space before Hours, one space before and one space after and, one space before Minutes.
-SQLBill
-
well i am doing a datediff so it looks like this
datediff(hh, soandsodate,soandsodate2) which this would return 25 hours... but i am looking for something similar that would return instead of 25 hours... it would return 1 day 1 hour... thanks
Moe C -
There is no easy method unless you create a function:
--aka
CREATE function fn_datediff_custom date1 datetime, date2 datetime
--with something like
select cast((datediff(hh, date1, date2) - datediff(hh, date1, date2) %24)/24 as varchar(20)) + ' Days ' +
cast(datediff(hh, date1, date2) % 24 as varchar(20)) + ' Hours'
-
How about this:
create function fnTime(@Hours int)
Returns varchar (8000)
as
begin
declare @t varchar(8000)
SELECT @t = CAST(@Hours/24 AS VARCHAR(2)) + ' Day(s)' + ' and ' + CAST(@Hours%24 AS VARCHAR(2)) + ' Hour(s)'
return(@t)
end
go
declare @t1 datetime
declare @t2 datetime
select @t1 = '01 Jan 2006 1:00:00.000',
@t2 = '02 Jan 2006 8:00:00.000'
select dbo.fnTime(datediff(hh, @t1, @t2))
-
Hi,
Use the following function which takes hours as input and returns the desired output to you..
Create FUNCTION Get_Day_Hour(@hr int) Returns Varchar(50)
as
Begin
Declare @Result varchar(250)
if substring(convert(varchar,convert(numeric(18,3),@hr)/24),1,patindex('%.%',convert(varchar,convert(numeric(18,3),@hr)/24))-1) > 0
Begin
set @result=convert(varchar,substring(convert(varchar,convert(numeric(18,3),@hr)/24),1,patindex('%.%',convert(varchar,convert(numeric(18,3),@hr)/24))-1)) + ' Day ' + convert(varchar,@hr - (convert(int,substring(convert(varchar,convert(numeric(18,3),@hr)/24),1,patindex('%.%',convert(varchar,convert(numeric(18,3),@hr)/24))-1)) * 24)) + ' Hours '
end
else
begin
set @result=convert(varchar,@hr) + ' Hours'
end
return @result
End
select dbo.Get_Day_Hour(45)
Result :
1 Day 21 Hours
Regards
AMIT GUPTA
-
thanks for all your help...
Moe C
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply