I'm thinking it's most likely that you have a piece or two of corrupted data, or a null that won't convert.
Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -----Original Message----- From: Dave Jones [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 1:58 PM To: CF-Talk Subject: RE: SQL stumper To all generously considering this problem, the example I used is admittedly an artificial one, clumsily extracted from a multi-part dynamically constructed query. I was attempting to isolate from the original query the portion causing the problem. The original query looks something like: SELECT a.1, a.2, b.1, b.2, c.1, c.2... FROM (a left join b) left join c... WHERE x=y AND y=x AND personID IN (SELECT personID FROM persons WHERE CAST('2003'+ SUBSTRING(CONVERT(char(8), dob, 112),5,4) as datetime) >= 'somestartdate' AND CAST('2003'+ SUBSTRING(CONVERT(char(8), dob, 112),5,4) as datetime) < 'someenddate' ) So it's not a simple matter of collapsing the sub-query into a single query. To repeat the original question, any idea why the subquery works stand-alone, but when included as a subquery throws the error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." TIA, Dave Jones NetEffect At 01:22 PM 3/25/03 -0500, you wrote: > > 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

