[thelist] SQL query question (easy)
Tab Alleman
talleman at Lumpsum.com
Wed May 10 09:38:10 CDT 2006
When you use OR, sql basically does a UNION for you behind the scenes. You actually get better performance by writing out the UNIONs, but it's a lot of code to look at.
Whenever NULLs are involved, I always first look to COALESCE:
WHERE GETDATE() BETWEEN COALESCE(start_on,'1/1/1900') AND COALESCE(end_on,'6/6/2079')
GETDATE() is TSQL for now(), by the way. I don't know what database uses now(), and I can only assume it uses COALESCE, or something like it.
> -----Original Message-----
> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of John Hicks
> Sent: Wednesday, May 10, 2006 2:01 AM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL query question (easy)
>
>
> April wrote:
> > I have start_on and end_on dates, which can also be null.
> I'm writing a
> > SQL select to find all instances where a date is within the
> start_on,
> > end_on range and but returns if one or the other is null
> too. If the
> > start_on is null and the end_on is greater than the date,
> then it needs
> > to return.
> >
> > Basically, I need to combine:
> > start_on <= now() AND end_on >= now()
> > with:
> > start_on IS NULL AND end_on IS NULL
> > start_on IS NULL AND end_on >= now()
> > start_on <= now() AND end_on IS NULL
>
> How about:
>
> where
> (start_on <= now() OR start_on IS NULL)
> AND (end_on <= now() OR end_on IS NULL)
>
> --J
>
> --
>
> * * Please support the community that supports you. * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>
More information about the thelist
mailing list