[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