getting the difference between time/date

Question

getting the difference between time/date

neotokyo

SSCertifiable

Points: 7239
More actions
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?
_________________________

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

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

RH-296080 Mr or Mrs. 500 Points: 527 More actions · Answer 1

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

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 2

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')

neotokyo SSCertifiable Points: 7239 More actions · Answer 3

checking this out now actually... thanks

_________________________

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

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

Glen Potter SSC Veteran Points: 232 More actions · Answer 5

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