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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

