Post reply

getting the difference between time/date

  • October 12, 2006 at 9:33 am

    #173174

    i have 2 date columns. [start_datetime], [end_datetime]

    in a query i would like to create another column which basically

    has the duration between. say for examnple hours, minutes etc.

    how is this done exactly? how do you get that exactly?

    thoughts?

    _________________________

  • October 12, 2006 at 9:42 am

    #665528

    try this. Column Names i used are StartDate and EndDate in Employee table. You can use a lot of dateparts - like mi,ss,hh . Look that up in SQL server help.

    select StartDate,EndDate,DATEDIFF ( ss , StartDate , EndDate ) DiffSeconds,

    DATEDIFF ( hh , StartDate , EndDate ) DiffHours

    from Employee

    RH

  • October 13, 2006 at 2:29 pm

    #665834

    Use datediff in units of the resolution desired, then integer division and modulo (remainder after integer division, the % operator) in an expression like:

    declare @olddatetime datetime

    declare @newdatetime datetime

    select @newdatetime = getdate()

    select @olddatetime = dateadd(minute, -65, @newdatetime) -- for illustration, expect 1:05

    select convert(varchar, datediff(minute, @olddatetime, @newdatetime) / 60) + ':' +

    replace(str(convert(float, datediff(minute, @olddatetime, @newdatetime) % 60), 2, 0), ' ', '0')

  • October 13, 2006 at 2:36 pm

    #665838

    checking this out now actually... thanks

    _________________________

  • October 13, 2006 at 3:44 pm

    #665853

    Depending on your data and query, it will likely be much more efficient to use a computed column to store the time difference in the table itself.

    ALTER TABLE Mytable
     ADD ElapsedTime AS (datediff(minute,[start_datetime], [end_datetime])) PERSISTED

    By adding Persisted to this definition, you will have a physical column with the data value that automatically updates anytime the [start_datetime] [end_datetime] column changes.  You can even index the column is you so choose.

    Now your query only has to convert the numeric value to the display format you want, like in Fred's example.  But if you are going to run this query with any frequency, you will not be slowing down the query by calculating the time difference for each row, each and every time.

    Hope this helps



    Mark

  • November 9, 2006 at 7:29 am

    #670834

    Does it make sense to anyone why this isn't a built-in option within SQL (MSAccess, SQL 2000, SQL 2005, PL/SQL)? Seems to me that a DateDiff formatted option (hh:mm:ss, mm/dd/yy) would be pretty easy to add to the library and simplify a whole lot of calculations. Look how conflugerated the CONVERT datetime options are.

    I have had to do this in the past to calculate hours worked and Mark is correct. The final solution is to either branch out the integral parts or to convert it all into a single integer (usually minutes unless seconds are required) and use that for all of your sums and totals and format to the display/print for the separation. This is not pretty when it is feeding Crystal Reports, etc.

    May even want to build a function all the way down to a final datetime format (dd:hh:mm) and a separate one for individual parts returned (@days, @hours, @mins). You don't want to have to replicate this in the future once you get it working.

    Ex:

    @minElapsed = 189

    @hourTotal = @minElapsed/60 -- 3 (As long as @minElapsed is an integer the decimals are truncated)

    @minTotal = @minElapsed % 60 -- 9

    --if @minElapsed >= 1440 (24 days * 60 minutes) you would need to offset for the hours for the number of days

    @hourTotal = ((@minElapsed % 1440)/60)

    @daysTotal = @minElapsed /1440 -- 0

    Cheers

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply