> 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
                                

Reply via email to