Comparing two date intervals that can have endate=null

[Smulan]

How do I compare two dateintervals (from-to date), the first intervall should be either fully or partly within the second date intervall, and in both intervals the enddate can be NULL.

Do not want to set the Null dates to an imaginary date if this can be avoided. Can it be done by SQL not using a lot of if and Case statements ? Have tried in VB and realised that It would take a darn lot of if statements.

 

[bperry]

How do you want to handle if either or both of the ToDates are Null?

i.e. Let's assume that FromDate1 is greater than FromDate2.

(1) If ToDate1 is Null, then what to do??

(2) If ToDate2 is Null, then what to do??

(3) If both Todates are Null, then what to do??

If you can mention to us jjust how these three conditions should be handled, then someone here can probably help you. For example, if Todate1 is Null, do you assume that it does, or does not, fall within ToDate2.

 

[VogonPoet]

where Case
when fromdt1<=fromdt2 and isnull(thrudtdt2,'12/31/2050')>=fromdt1 then 1
when fromdt1<=isnull(thrudt,'12/31/2050') and isnull(thrudt2,'12/31/2050')>=isnull(thrudt1,'12/31/2050') then 1
when fromdt1>=fromdt2 and isnull(thrudt1,'12/31/2050')<=isnull(thrudt2,'12/31/2050') then 1
else 0 end = 1

Michael

 

[Smulan]

If we start with the statement:

the first intervall should be either fully or partly within the second date intervall, and in both intervals the Todate can be NULL.

we can have the intervalls DateFrom1 - DateTo1 and DateFrom2 - DateTo2.

if DateTo1 or DateTo2 are NULL then the intervall should be read as from the FromDate to eternity of time. that is if the DateFrom1 = 01/01/2002 and DateTo1= NULL then the first intervall is valid from the date 01/01/2002 - and forward. The same thing is for the second intervall.

(1) That is If DateTo1= NULL It is within the second intervall if the DateFrom1 is smaller than the DateTo2

(2) If DateTo2=NULL, the first intervall is within the second intervall if the DateTo1 is larger than the DateFrom2

(3) If DateTo1=NULL and DateTo2=NULL then The first intervall is within the second interval.

(4) None of the DateTo is null, do a ordinary comparement. between the date intervalls

OK now I can make a IF/case statment of the whole thing Thanks for sorting out my thoughts I will return with a nice code examlple.

 

[Smulan]

Here is the solution to my problem, I didn´t do it in SQL since I´m more comfortable in VB. But I guess that it is easy for a SQL programmer to convert this to SQL.


Dim dateFrom1 As Variant
Dim dateTo1 As Variant
Dim dateFrom2 As Variant
Dim dateTo2 As Variant
Dim blnDateIntervalIsWithin As Boolean

dateFrom1 = m_rs.fiels(&quot;DATE_FROM&quot.value
dateTo1 = m_rs.fiels(&quot;DATE_TO&quot.value
dateFrom2 = m_rs2.fiels(&quot;DATE_FROM&quot.value
dateTo2 = m_rs2.fiels(&quot;DATE_TO&quot.value

blnDateIntervalIsWithin = False

If IsNull(dateTo1) And IsNull(dateTo2) Then
'BOTH to dates is null
blnDateIntervalIsWithin = True
ElseIf Not IsNull(dateTo1) And Not IsNull(dateTo2) Then
'NONE of the todates is null
If dateFrom1 = dateFrom2 Or dateFrom1 = dateTo2 Or dateTo1 = dateFrom2 Or dateTo1 = dateTo2 Then
blnDateIntervalIsWithin = True
ElseIf dateFrom1 < dateFrom2 And dateFrom2 < dateTo1 Then
blnDateIntervalIsWithin = True
ElseIf dateFrom1 > dateFrom2 And dateTo2 > dateFrom1 Then
blnDateIntervalIsWithin = True
End If

ElseIf IsNull(dateTo1) And Not IsNull(dateTo2) Then

If dateFrom1 <= dateTo2 Then
blnDateIntervalIsWithin = True
End If
ElseIf Not IsNull(dateTo1) And IsNull(dateTo2) Then
If dateTo1 >= dateFrom2 Then
blnDateIntervalIsWithin = True
End If
End If

Thanks for the awnsers it helped me a lot! CU