CODEWITHSUNDEEP
sql-servert-sql
wingyip
wingyip

Reputation: 3536

Query using a date range but end date may be null

sql server 2014

I am trying to query data using a date range. Data in the table is datetime datatype.

so I want to use as parameters @IncidentDate and @IncidentEndDate.

Issues are that the @IncidentEndDate may be null. Also each row in the data may or may not have an end_datetime (null if no date)

In my where clause I have 

(end_datetime IS NULL) AND (@IncidentDate >= CAST(start_datetime AS DATE) AND @IncidentDate <= DATEADD(d,0,DATEDIFF(d,0,start_datetime)))
OR
(@IncidentDate <= end_datetime) AND (@IncidentEndDate >= start_datetime)

However I am not sure if this si working properly. I would expect rows that have no end_datetime to appear in the results but they don't seem to be .

EDIT: In the end I came up with the following after reading everybody's replies...

WHERE (
      @IncidentDate <= isnull(end_datetime, dateadd(day,1,start_datetime))
   ) 
   AND (
   isnull(@IncidentEndDate,dateadd(day,1,@IncidentDate)) >= start_datetime
   )

This seems to me to be a tidier way to satisfy my requirements - it looks after the possiblity of both end_datetime being null and @IncdidentEndDate being Null

Upvotes: 0

Views: 1225

Answers (2)

user4650451
user4650451

Reputation:

Also, you can use the ISNULL() function to help handle NULL values. The ISNULL() function checks the value and, if it's NULL, replaces it with a supplied value. So if you wanted the NULLs, you could put in a value that would match or, if not, something that would definitely be outside your range like 01/01/1900 or the MIN Date (varies depending on DATETIME or DATETIME2).

To exclude:

SELECT * FROM mySweetTable WHERE ISNULL(createdDate, '01-JAN-1900') >= '01-JAN-2015'

To include:

SELECT * FROM mySweetTable WHERE ISNULL(createdDate, GETDATE()) >= '01-JAN-2015'

Although, I don't recommend ACTUALLY leaving GETDATE() in the WHERE clause, that's bad news bears; replace it with a variable or specific value.

Upvotes: 2

S3S
S3S

Reputation: 25112

--This should get you what you want

start_datetime >= @IncidentDate and (@IncidentEndDate is null or end_datetime is null or end_datetime < dateadd(day,1,@IncidentEndDate))

Unless, of course, you want to exclude ones where the end_datetime is null and if that's the case just let me know!

Upvotes: 0

Related Questions