James,
I used an artificial example in an attempt to isolate the 
problem. In the real query, which is build dynamically, I 
considered your approach, but it gets messy if the date range 
spans two months (e.g. 7/28 - 8/2) or two years (12/27/02 - 1/4/03)

Dave Jones
NetEffect


At 10:33 AM 3/25/03 -0800, you wrote:
>OR better yet,
>
>SELECT  personID, dob
>FROM            persons
>WHERE           (Month(dob) = 7)
>   AND           (Day(dob) BETWEEN 21 AND 28)
>
>No more yucky cast/converts and other string ops. :)
>
>-------------------------------
>James Ang
>Sr. Developer/Product Engineer
>MedSeek, Inc.
>[EMAIL PROTECTED]
>
>
>
>-----Original Message-----
>From: Dave Watts [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, March 25, 2003 10:23 AM
>To: CF-Talk
>Subject: RE: SQL stumper
>
>
> > I'm stumped by a SQL sub-query that works stand-alone,
> > but not when assembled into a query.
> >
> > This works:
> > SELECT personid, dob
> > FROM persons
> >
> > And this works:
> > (SELECT personID FROM persons
> > WHERE
> > CAST('2003'+ SUBSTRING(CONVERT(char(8), dob, 112),5,4) as
> > datetime) >= '2003-07-21'
> > AND
> > CAST('2003'+ SUBSTRING(CONVERT(char(8), dob, 112),5,4) as
> > datetime) < '2003-07-28'
> > )
> >
> > But when assembled together as below, it throws this error
> > message: "The conversion of a char data type to a datetime
> > data type resulted in an out-of-range datetime value."
> >
> > SELECT personid, dob
> > FROM persons
> > WHERE personID IN
> > (SELECT personID FROM persons
> > WHERE
> > CAST('2003'+ SUBSTRING(CONVERT(char(8), dob, 112),5,4) as
> > datetime) >= '2003-07-21'
> > AND
> > CAST('2003'+ SUBSTRING(CONVERT(char(8), dob, 112),5,4) as
> > datetime)+5 < '2003-07-28'
> > )
> >
> > Can anyone shed some light on this?
>
>No, but it seems to me that you don't need a subquery at all:
>
>SELECT  personID, dob
>FROM            persons
>WHERE           CAST('2003'+ SUBSTRING(CONVERT(char(8), dob, 112),5,4)
>                 as datetime) >= '2003-07-21'
>AND             CAST('2003'+ SUBSTRING(CONVERT(char(8), dob, 112),5,4)
>                 as datetime) < '2003-07-28'
>
>Dave Watts, CTO, Fig Leaf Software
>http://www.figleaf.com/
>voice: (202) 797-5496
>fax: (202) 797-5444
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

                                Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
                                

Reply via email to