I'm trying to move some logic that I have currently within a program and putting it into SQL instead.

My table has the following 3 fields that are of interest to me: StartDate (DateTime), StopDate(DateTime), Length (int)

Length is calculated based on StopDate - StartDate and is expressed to the nearest minute.

What I want to do is query the Db giving a start date and end date and return all records that fall within that date range. I then want to present that data such that the earliest date is set to the start date criteria, the last to the end date criteria and the length recalculated.

Say for example I query for results between 01/02/2015 07:00:00 and 01/02/2015 19:00:00 and I get the following:

Start Stop Length

01/02/2015 06:30:00 01/02/2015 07:05:00 35
01/02/2015 07:05:00 01/02/2015 10:00:00 175
01/02/2015 10:00:00 01/02/2015 19:15:00 555

I would like the output to show as

Start Stop Length

01/02/2015 07:00:00 01/02/2015 07:05:00 5
01/02/2015 07:05:00 01/02/2015 10:00:00 175
01/02/2015 10:00:00 01/02/2015 19:00:00 540

Is there a way to do this?

Try (from the top of my head, the dates conditions can be simplified):

declare @t table (startDt datetime, endDt datetime, [length] int)

insert into @t values ('01/02/2015 06:30:00', '01/02/2015 07:05:00',35),
('01/02/2015 07:05:00', '01/02/2015 10:00:00', 175),
('01/02/2015 10:00:00', '01/02/2015 19:15:00', 555)

declare @start datetime = '01/02/2015 07:00:00' , @end datetime = '01/02/2015 19:00:00'

;with cte as (select *, case when @start between startDt and endDt then @start else startDt end as newStartDt,
case when @end between StartDt and EndDt then @end else EndDt end as NewEndDt
from @t where @start between StartDt and EndDt or @end between StartDt and EndDt
or (StartDt >=@start and EndDt<=@end))

select *, datediff(minute, newStartDt, newEndDt) as NewLength from cte 

Hi Vehlin good Morning,

I have what you are looking for! Please see the following code below,

declare @StartDate datetime = '2015-02-01 07:00:00'; 
declare @EndDate datetime = '2015-02-01 19:00:00';


With MyTableTemp (DateStart, DateEnd)
as
(
	Select '2015-02-01 06:30:00','2015-02-01 07:05:00'
	union all 
	select '2015-02-01 07:05:00', '2015-02-01 10:00:00'
	union all 
	select '2015-02-01 10:00:00', ' 2015-02-01 19:15:00'
)


select 

	case when PreviousRow is null then ParamStart
	else PreviousRow end as [Start],
	
	case when PreviousRow is null or diff>0  then DateEnd
	else ParamEnd end as [Stop],

	case when PreviousRow is null then datediff(Minute,ParamStart,DateEnd)
	When diff <0 then datediff(Minute,PreviousRow,ParamEnd)
	else datediff(Minute,PreviousRow,DateEnd) end as Lenght
	
from 
(
	select 
		datediff(MINUTE,DateEnd,ParamEnd) diff ,
		lag(DateEnd) over (order by DateStart) PreviousRow,* from 
	(
		Select  
			@StartDate ParamStart,
			@EndDate ParamEnd,
			DateStart,
			DateEnd
		from 
			MyTableTemp 
		where 
			convert(date,DateStart)>=convert(date,@StartDate) and convert(date,DateEnd)<=convert(date,@EndDate)

	) as q1 
) as q2

Result

>> I'm trying to move some logic that I have currently within a program and putting it into SQL instead. <<

This is a great idea. 85-90% of the work can be done in the DDL! Hooray! You are moving from Procedural programming to Declarative programing! This is like moving from the Dark Ages to the Enlightenment! 

>> My table has the following 3 fields [sic] that are of interest to me: event_start_(DATETIME2(0)TIME), event_end_DATETIME2(0)(DATETIME2(0)TIME), Length (INTEGER) Length is both a reserved word in SQL and calculated value based on event_end_- event_start_date and is expressed to the nearest minute. <<

Okay, you did not make it completely out of the dark Ages :( It is a big jump. First, actually read a book on SQL so you will know that in SQL a field is part of a temporal value {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND} and not a column. 

You failed to use the only display format for temporal values in ANSI/ISO Standard SQL. It is the ISO-8601 Standard; next to the Metric system, this is the most common IT standard on earth. And your ignorance means we have to re-type your posting. Here is an attempt to guess. 

CREATE TABLE Events
(event_name CHAR(10) NOT NULL PRIMARY KEY,
 event_start_timestamp DATETIME2(0) NOT NULL
 CHECK (event_start_timestamp IN ('00:00:00',.. '23:59:00')),
 event_end_timestamp DATETIME2(0) NOT NULL
 CHECK (event_end_timestamp IN ('00:00:00',.. '23:59:00')),
 CHECK (event_start_timestamp < event_end_date));

See how DDL gets rid of seconds! Why store bad data? 

Did you notice that there is no poorly named length column here? First to all, time is not a length; it is a duration. Think about the temporal dimensions and how they are different from spatial dimensions. 

But the real error is storing a computation in a table. The purpose of all Databases (not just SQL!) is to reduce redundancy, not increase it. 

>> What I want to do is query the database giving a start date and end date and return all records [sic: rows are not records!] that fall within that date range. << 

What does that mean? Google the OVERLAPS predicate in ANSI/ISO Predicate and Rick Snodgrass for definitions of temporal relationships.