SQL date select between null parameter

Transact-SQL
1

Hello!

I have a table with StartDate and EndDate fields. If these values are not null I can select records that are between @StartDate AND @EndDate that are not null as well.

DECLARE @StartDate nvarchar(8)
SET @StartDate=null
DECLARE @EndDate nvarchar(8)
SET @EndDate=null

SELECT D.ID, D.StartDate, D.EndDate FROM D
WHERE
(@StartDate BETWEEN D.StartDate AND D.EndDate)
OR
(@EndDate BETWEEN D.StartDate AND D.EndDate)
OR
(D.StartDate BETWEEN @StartDate AND @EndDate)

But how could I make the select when either @StartDate OR @EndDate Or the record values can be null?
If @StartDate OR @EndDate is null that means not interested in either startdate or enddate of the event.
If record values are null that means the event is in progress and no end date.

Thank you!

2

Try something like this

(@StartDate BETWEEN D.StartDate AND D.EndDate) OR (@StartDate IS NULL)
...
3

Assuming StartDate and EndDate fields are date (or datetime):

select D.ID
      ,D.StartDate
      ,D.EndDate
  from D
 where D.StartDate<dateadd(dd,1,cast(isnull(@EndDate,'99991230') as date))
   and D.EndDate>cast(isnull(@StartDate,'17530101') as date)
4

Are your StartDate and EndDate columns DATE or DATETIME datatype? If not it would be much better that they are. Then change your parameters @StartDate and @EndDate to the same data type.

If your datatype is DATETIME then you need to change the "endpoint" to compare LESS THAN "tomorrow", rather than LESS THAN OR EQUAL to "Last Day/time". That is all a bit fiddly ... so using DATE is easier.

Either way, don't use STRING dates at all. All sorts of possibilities for SQL to parse them wrongly, if you want to calculate the interval between dates, or use some other date-function on them, they have to be parsed and converted to DATE / DATETIME first, which is slow (and as said error prone - what happens if you have "20150231" in your VARCHAR?