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