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