question

node18 avatar image
node18 asked

Problem with Between dates and Null values

I have a database with lots of Null date values in my end_date column. I wan to select all records where a date is between two set dates: Start_Date and End_Date or between Start_Date and End_Date where End_Date IS NULL Why can't I do something like this
WHERE (date BETWEEN records.Start_Date AND (records.End_Date OR records.End_Date IS NULL)
or my other way of thinking is
WHERE (date BETWEEN records.Start_Date AND records.End_Date) OR 
(date BETWEEN records.Start_Date AND records.End_Date IS NULL)
I assume it's something to do with the logic. Anyone able to lend a hand?
sql-server-2005sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
I prefer where date is between records.Start_Date and isnull(records.End_Date, date) which is somewhat like what you originally tried to write with your OR statement
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Kev (+1), very concise and no messy OR! I think that this is the best.
0 Likes 0 ·
Yes, just what I was looking for!
0 Likes 0 ·
@node18, it looks like you like Kev's answer best. Please click on the sign that marks this one as the answer. It's below where the thumbs up and down mini-buttons are.
0 Likes 0 ·
Tim avatar image
Tim answered
How about WHERE (date BETWEEN records.Start_Date AND records.End_Date) OR records.End_Date IS NULL If you also want records with a Start_Date greater than a certain date and where End_Date is NULL then WHERE (date BETWEEN records.Start_Date and records.End_Date) or (date > records.Start_Date and End_Date IS NULL)
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Trad your first one wouldn't work
0 Likes 0 ·
And your second could fail if date = start_date
0 Likes 0 ·
@Trad : nice try on the edit, but now the first one fails if the date is before the start_date
0 Likes 0 ·
Mark avatar image
Mark answered
Since you can't say BETWEEN a date and a NULL value, you can slightly change this to say: WHERE (date BETWEEN records.Start_Date AND records.End_Date) OR ( date >= records.Start_Date AND records.End_Date IS NULL) edit: had this backwards, changed to "date >= records.Start_Date" as Kev said.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Mark that won't work - you've got the '>=' the wrong way round :)
0 Likes 0 ·
@Kev, curses, had it backwards as you said. Thanks Kev.
0 Likes 0 ·
Thanks a lot
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.