Problem with Between dates and Null values

Question

question

node18 asked Jan 13, '11

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-2005 sql

10 |1200

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

Answer 1 · 2011-01-13T11:14:00Z

Kev Riley answered Jan 13, '11

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

10 |1200

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

Mark commented · Jan 13, 2011 at 12:30 PM

@Kev (+1), very concise and no messy OR! I think that this is the best.

0 ·

node18 commented · Jan 14, 2011 at 06:35 AM

Yes, just what I was looking for!

0 ·

Mark commented · Jan 14, 2011 at 06:53 AM

@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 ·

Answer 2 · 2011-01-13T10:32:06Z

Tim answered Jan 13, '11

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

10 |1200

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

Kev Riley ♦♦ commented · Jan 13, 2011 at 11:18 AM

@Trad your first one wouldn't work

0 ·

Kev Riley ♦♦ commented · Jan 13, 2011 at 11:25 AM

And your second could fail if date = start_date

0 ·

Kev Riley ♦♦ commented · Jan 13, 2011 at 11:31 AM

@Trad : nice try on the edit, but now the first one fails if the date is before the start_date

0 ·

Answer 3 · 2011-01-13T10:40:19Z

Mark answered Jan 13, '11

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

10 |1200

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

Kev Riley ♦♦ commented · Jan 13, 2011 at 11:19 AM

@Mark that won't work - you've got the '>=' the wrong way round :)

0 ·

Mark commented · Jan 13, 2011 at 12:23 PM

@Kev, curses, had it backwards as you said. Thanks Kev.

0 ·

node18 commented · Jan 14, 2011 at 06:34 AM

Thanks a lot

0 ·

question