> 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