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
                                

Reply via email to