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

