We help IT Professionals succeed at work.

get the time differencial between two hours

COHFL
COHFL asked
on
Medium Priority
228 Views
Last Modified: 2012-06-27
hi there,
I have stored two dates  on my table and it looks like this:
event_time                        event_time_end
1900-01-01 10:20:00.000 1900-01-01 10:30:00.000

notes: this two hours are not always 15 minutes it varies hence the reason why i need to get the time difference between the two.
how can i get the difference between the two?
Thanks,
COHFL
Comment
Watch Question

CERTIFIED EXPERT

Commented:
select DATEDIFF(MINUTE,'1900-01-01 10:20:00.000', '1900-01-01 10:30:00.000')

Open in new window

You could divide by 60 to get the hours like this:
DATEDIFF(MINUTE,'1900-01-01 10:20:00.000', '1900-01-01 10:30:00.000')/convert(real,60)
And you'll probably want to replace the time string with field names from your table. Something like:
select DATEDIFF(MINUTE,event_time, event_time_end)/convert(real,60) from event_times
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
select datediff(minute, date1, date2)

Author

Commented:
Nicobo,
the only problem with doing the division by 60 if i run this:
select DATEDIFF(MINUTE,'2008-01-01 12:00:00', '2008-01-01 12:15:00')/convert(real,60)
i get 0.25 where it should display 0.15
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
ok that did it.
iuse your suggestion:
select dateadd(MINUTE, DATEDIFF(MINUTE,'1900-01-01 10:20:00.000', '1900-01-01 10:35:00.000'), '1900-01-01')

and i add a small rinkle to filter out the time portion of it so it looks cleaner:

select CONVERT(VARCHAR(8), dateadd(MINUTE, DATEDIFF(MINUTE,'1900-01-01 08:20:00.000', '1900-01-01 10:35:00.000'), '1900-01-01'), 108)

Thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.