Imagine this: DOB = 02/29/2000
Now, doing your cast/convert thing, you will get produce 02/29/2003 which is completely out of range. :P Leap year babies are so lucky! :P And watch out how you are producing 'somestartdate' and 'someenddate' to make sure that the dates are valid dates. Another suggestion to debug this would be to provide and use aliases for your tables and prefix the columns with the alias. Run the SQL code in MS SQL Query Analyzer (assuming that you are using MS SQL Server) with Show Execution Plan turned on and see what kind of information you are getting. :P ------------------------------- James Ang Sr. Developer/Product Engineer MedSeek, Inc. [EMAIL PROTECTED] -----Original Message----- From: Dave Jones [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 10:58 AM 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

