Sql-server – SQL Query with multiple conditions on date

datetimesql server

I want to write a query with condition on dates for below table and sometimes it requires great thinking.

StartDtm                             StopDtm
2013-11-03 00:00:00.000               NULL
2013-11-05 08:00:00.000               NULL
2013-11-18 09:00:00.000               NULL
2013-11-18 08:00:00.000               NULL
2013-11-19 08:00:00.000              2013-11-26 07:59:00.000
2013-11-20 08:00:00.000              2013-11-27 07:59:00.000
2013-11-19 08:00:00.000              2013-12-19 07:59:00.000

The table shows the task start date and task end date. Start date can never be null.
Last record shows that the task starts at 19th Nov. and will end at 19th Dec. So if I pass any dates, it should show all the tasks which need to be performed on any date between passed date range.

Conditions are as below:

1.Any date or both date parameters may be null. If so all data should come.

2.If @StartDtm is null and @StopDtm is passed, then it should bring all data based on StopDtm field only but it should not bring those data whose StartDtm is greater than @StopDtm. Same for @StopDtm parameter

3.If StopDtm field is null, It should bring all data based on StartDtm field only

I tried few ways but not succeeded. I to keep condition as short as possible. If possible, then please provide multiple queries with little explanation.

My query is as below:

DECLARE @StartDtm datetime='2013-11-15'
DECLARE @StopDtm datetime='2013-11-21'

select * from #TempTbl
where  ((StartDtm<=@StartDtm OR @StartDtm IS NULL)  AND (StopDtm>=@StopDtm OR StopDtm IS NULL))
    OR((StartDtm>=@StartDtm OR @StartDtm IS NULL) AND (StopDtm>=@StopDtm AND StartDtm<= @StopDtm OR StopDtm IS NULL OR @StopDtm IS NULL))
    OR((StartDtm>=@StartDtm OR @StartDtm IS NULL) AND (StopDtm<=@StopDtm OR StopDtm IS NULL OR @StopDtm IS NULL))

Thanks in advance..
The question is already asked in StackOverflow but I cannot find how to migrate it here. So asked here again.

Created another query which seems working fine:

DECLARE @StartDtm datetime='2013-11-20'
DECLARE @StopDtm datetime='2013-11-30'

IF(@StartDtm is null)
    SET @StartDtm='1900-01-01'
IF(@StopDtm IS NULL)
    SET @StopDtm='9999-12-31'

select * from #TempTbl
where (startDtm<= @StartDtm and ISNULL(StopDtm,@StopDtm)>=@StopDtm )
  OR(StartDtm>=@StartDtm and ISNULL(StopDtm,@StopDtm)<=@StopDtm )
  OR(StartDtm between @StartDtm AND @StopDtm and ISNULL(StopDtm,@StopDtm)>=@StopDtm  )
  OR(StartDtm<=@StartDtm AND ISNULL(StopDtm,@StopDtm) between @StartDtm AND  @StopDtm)

Suggest if any logical error/change/improvement.

Best Answer

You can use isnull to take care of the parameters when they are null.

Something like this should do what you want.

select T.*
from #TempTbl as T
where T.StartDtm <= isnull(@StopDtm, '99991231') and 
      (T.StopDtm >= isnull(@StartDtm, '17530101') or T.StopDtm is null)