[cfaussie] Re: look up date by years old with a 2 year spread < 2 > 2

Steve Onnis Tue, 18 Mar 2003 05:37:54 -0800

Greg

you would better off passing in a value for the time span, as in oler and/or younger values

So the form would submit, "I want all people older than 18 and younger that 30"

the query would look like this

<CFQUERY name="getRecord" dataSource="interact">
SELECT Talent.talentID ,
   Talent.Name ,
   Talent.Gender ,
   Talent.DOB ,
   Talent.Hair ,
   Talent.Eye ,
   Talent.Height ,
   Talent.Clothssize ,
   Talent.shoesize ,
   Talent.hobbies ,
   Talent.BIO ,
   Talent.Compcard ,
   Talent.ID
FROM Talent
WHERE 1=1
<CFIF ParameterExists(url.ID)>AND Talent.ID = #url.ID#</CFIF>
<CFIF ParameterExists(form.search)>
  AND Talent.gender = '#form.gender#'
  
  
  <CFIF Form.OlderThan AND Form.YoungerThan>
   AND dob BETWEEN DateAdd(Year,#Form.OlderThan#,dob) AND DateAdd(Year,#Form.YoungerThan#,dob)
  <cfif>
  
  <cfif Form.YoungerThan AND NOT Form.OlderThan>
   AND dob < DateAdd(Year,#Form.YoungerThan#,dob)
  </cfif>
  
  <cfif NOT Form.YoungerThan AND Form.OlderThan>
   AND dob > DateAdd(Year,#Form.OlderThan#,dob)
  </cfif>
   <CFIF form.hair IS NOT "">and talent.hair = '#form.hair#'</CFIF>
</CFIF>

</CFQUERY>

SQL has its own DateAdd() function, so dont put and hashes(#) around them in the query. this way you are making the comparison directly against the DOB column

See how you go

Regards

Steve Onnis
Domain Concept Designs
+61 422 337 685
+61 3 9431 4249

http://www.domainconceptdesigns.com [EMAIL PROTECTED]		http://www.cfcentral.com.au [EMAIL PROTECTED]

("If you think it can't be done, you haven't asked me!") - Steve Onnis

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, 18 March 2003 9:24 PM
To: CFAussie Mailing List
Subject: [cfaussie] Re: look up date by years old with a 2 year spread < 2 > 2

Hi

Query by years old on dob field

search by years old with a 2 year spread each side of there date of birth .

eg: 5 years old Query = select any member between 3 years and 7

The data base is set-up with DOB field

eg 31/12/1997 Query = select any user between 31/12/1995 and 31/12/1999

I cant work out the less than 2 years and more than 2 years with the date field

i would like to send a number to the query but it could be a year or????

Hope all this is clear and thanks in advance if anyone can help on my short comings with date issues and queries.

<CFQUERY name="getRecord" dataSource="interact">
SELECT Talent.talentID , Talent.Name , Talent.Gender , Talent.DOB , Talent.Hair , Talent.Eye , Talent.Height , Talent.Clothssize , Talent.shoesize , Talent.hobbies , Talent.BIO , Talent.Compcard , Talent.ID
FROM Talent
<CFIF ParameterExists(url.ID)>
WHERE Talent.ID = #url.ID#
</CFIF>

<CFIF ParameterExists(form.search)>
WHERE Talent.gender = '#form.gender#'

<CFIF form.dob IS NOT "">
and talent.dob is #form.dob# ( could be a number or a year )
</CFIF>

<CFIF form.hair IS NOT "">
and talent.hair = '#form.hair#'
</CFIF>

</CFIF>

</CFQUERY>

Kind Regards
Greg Stone
Australian Business Web
www.abw.net.au
[EMAIL PROTECTED]
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

[cfaussie] Re: look up date by years old with a 2 year spread < 2 > 2

Reply via email to