For those interested in converting a person's DOB to the current's birthdate in T-SQL, here is one example:
DateAdd(yy, (Year(getdate())-Year(dob)), dob) We are assuming that dob can be converted to either datetime or smalldatetime, and it is not null. ;) When I ran the above in MS SQL Server 2000, a dob of 02/29/2000 would yield 02/28/2003. :) ------------------------------- James Ang Sr. Developer/Product Engineer MedSeek, Inc. [EMAIL PROTECTED] -----Original Message----- From: Dave Jones [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 11:42 AM To: CF-Talk Subject: RE: SQL stumper SOLVED! James, You are the winner! >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 This was the clue that lead down the proper path. The dates constructed by the sub-query needed to be masked against constructing invalid dates. Thanks very much for the help! Dave Jones NetEffect >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
