title: Why doesn't the procedure return the data ?
body: <p>I have been working on this query which should return data that falls between specified daterange but it doesn't. It returns the data that whether falls or not between range.</p>
  
  <p>@DateTo
  @DateFrom are of DateTime type;</p>
  
  <pre><code>If(@fk_UserRoles_WebUsers_RoleID = 1) BEGIN
  SELECT Cases.pk_Cases_CaseID,
         Cases.CaseTitle,
         Cases.CasePetitioner,
         Cases.CaseNo,
         Cases.CaseLetterBy,
         Cases.CaseEntryDateTime,
         CaseHearings.pk_CaseHearings_ID,
         CaseHearings.Responder,
         CaseHearings.Supervisor,
         CaseHearings.Judge,
         CaseHearings.IsOpeningDate,
         CaseHearings.IsOpeningClosingDate,
         Convert(varchar, CaseHearings.ClosingDate, 106) AS ClosingDate,
         Convert(varchar, CaseHearings.ReOpeningDate, 106) AS ReOpeningDate,
         Convert(varchar, CaseHearings.OpeningDate, 106) AS OpeningDate,
         CaseHearings.IsConveyed,
         CaseHearings.IsClosingDate,
         Convert(varchar, CaseHearings.HearingDate, 106) AS HearingDate,
         Cases.fk_ConcernedOffices_Cases_ConcernedOfficeID,
         CaseHearings.CourtOrder,
         CaseHearings.CaseRemarks,
         ConcernedOffices.ConcernedOfficeName,
         Courts.CourtName,
         Convert(varchar,CaseHearings.GeneralDate, 106) AS GeneralDate,
         CaseHearings.IsHearingDate
  FROM Cases
  LEFT JOIN CaseHearings ON Cases.pk_Cases_CaseID= CaseHearings.fk_Cases_CaseHearings_CaseID
  AND ((@DateFrom =''
        AND @DateTo ='')
       OR (Cases.CaseEntryDateTime &gt;= @DateFrom
           AND Cases.CaseEntryDateTime &lt; DATEADD(DAY, 1, @DateTo) ) )
  LEFT JOIN ConcernedOffices ON ConcernedOffices.pk_ConcernedOffices_ID= Cases.fk_ConcernedOffices_Cases_ConcernedOfficeID
  LEFT JOIN Courts ON Courts.pk_Courts_CourtID= CaseHearings.fk_Courts_CaseHearings_CourtID
  INNER JOIN Web_Users ON Web_Users.UserID= Cases.fk_WebUsers_Cases_UserID
  WHERE (@CaseNo =''
         OR (@CaseNo!=''
             AND Cases.CaseNo LIKE '%'+ @CaseNo +'%'))
    AND Cases.CaseTitle LIKE '%'+@CaseTitle+'%' END
  </code></pre>