2 start and 2 end dates difference. sql server -

i have 4 dates. example.

2 start dates , 2 end dates, like:

start_date_he 2013-08-15 01:24:00.000     end_date_he 2013-08-15 02:09:00.000 

and

start_date_lunch     2013-08-14 20:40:00.000   end_date_luch 2013-08-14 21:40:00.000 

i wanna know hou many minutes have between these dates.

in sample might 0. because 20:40 until 21:40 not between 01:24 until 02:09

here version, seems mess, work values:

select l.id lunchid , isnull(datediff(mi,     case when l.startdate between h.startdate , h.enddate l.startdate else         (case when l.startdate < h.startdate h.startdate else null end) end,     case when l.enddate between h.startdate , h.enddate l.enddate else         (case when l.enddate > h.enddate h.enddate else null end) end),     0) result lunch l left join h on h.id = 1  

or if going use more in 1 query, may better create function

create function getbetweenminutes(     @hstart datetime,     @hend datetime,     @lstart datetime,     @lend datetime) returns int begin     declare @diffstart datetime     declare @diffend datetime      if (@lstart between @hstart , @hend)         set @diffstart = @lstart     else         begin             if (@lstart < @hstart)                 set @diffstart = @hstart             else                 set @diffstart = null         end      if (@lend between @hstart , @hend)         set @diffend = @lend     else         begin         if (@lend > @hend)             set @diffend = @hend         else             set @diffend = null         end      return isnull(datediff(mi,@diffstart,@diffend),0) end; 

here sql fiddle sample of data