SQL Query with multiple conditions on date

Question

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)
begin
 SET @StartDtm='1900-01-01'
end
IF(@StopDtm IS NULL)
begin
 SET @StopDtm='9999-12-31'
end
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.

Answer 1

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)

• Microsoft Developer: Its not working. For given parameters, it should give all records but your query does not give all records
• Mikael Eriksson: @ChiragFanse Don't know what you mean. It does return all 7 records from your sample data. Check this SQL Fiddle
• Microsoft Developer: @Mikael..Ohh sorry. it was my mistake. It gives all records. Still need to check other condition. I have also developed another query which is posted in question with edit. Please have a look at it once. Suggest if any change/improvement
• Mikael Eriksson: @ChiragFanse Too much logic in your where clause for my brain to parse. I can however tell you that your query will not be able to use a index on StopDtm (or StartDtm) in the case it is needed for performance reasons. I would guess that a index with StopDtm as leading column would be the best for you create index IX_TempTbl_StopDtm on TempTbl(StopDtm, StartDtm).