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
Get the mailserver that powers this list at http://www.coolfusion.com

                                Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
                                

Reply via email to