get the time differencial between two hours

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
COHFLAsked:
Who is Participating?
 
Nico BontenbalCommented:
Something like this than maybe:
select dateadd(MINUTE, DATEDIFF(MINUTE,'1900-01-01 10:20:00.000', '1900-01-01 10:35:00.000'), '1900-01-01')
returns the difference as a time. Will only work if the difference is less than a day.
Or what about:
select convert(varchar(3),DATEPART(HOUR, dateadd(HOUR, DATEDIFF(HOUR,'1900-01-01 09:20:00.000', '1900-01-01 10:35:00.000'), '1900-01-01'))) + ' hours and ' +  convert(varchar(3),DATEPART(MINUTE, dateadd(MINUTE, DATEDIFF(MINUTE,'1900-01-01 09:20:00.000', '1900-01-01 10:35:00.000'), '1900-01-01'))) + ' minutes'


0
 
Nico BontenbalCommented:
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
0
 
Alpesh PatelAssistant ConsultantCommented:
select datediff(minute, date1, date2)
0
 
COHFLAuthor 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
0
 
COHFLAuthor 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.