Hey Nick - Tried this in MS Access:-
- Created three records in Employee table and had their Date of Births
stored in column named DOB.
- To calculate which one of them is more than 30 yrs old ( not me..;)
---------------------------
SELECT *
FROM Employee
WHERE DateDiff("yyyy",[Employee.DOB],#12/30/2006#)>30;
---------------------------
So I guess you will have to try the same but try your CF code to Ignore
# in front of 12/30/2006 or whatever 9/30/2006 by putting it like this
and see if it works.. ##12/30/2006##
Let me know how it goes, I will try to make a CFQUERY and access my
EMPLOYEE Database to see if it works..
Thanks.
-Sandy Vohra
-----Original Message-----
From: Nick G [mailto:[EMAIL PROTECTED]
Sent: Friday, December 29, 2006 12:16 PM
To: CF-Newbie
Subject: Re: CF Query Select Strings
Hi Sandy,
the format of Scd is 2/15/1987 <<< thats how it appears in the database.
I wasn't too sure of how you wanted me to select DateDiff('yyyy',
#DateFormat(TD, "mm/dd/yyyy")#, Scd) so this is what I did (I used TOP 3
to limit the number of results):
<cfset TD="09/30/2006">
<cfquery name="r1" datasource="DB">
Select TOP 3 DateDiff('yyyy', #DateFormat(TD, "mm/dd/yyyy")#, Scd) AS
Num From Employees
</cfquery>
<cfloop query="r1">
<cfoutput>#Num#<br /></cfoutput>
</cfloop>
I got these results:
103
107
93
The first three Scd records in the database are all 2/15/1987
Thank you for helping me out with this by the way :)
>Nick - Can you please confirm -
>
>- In what format, you have date in Database in column Scd?
>
>- What you get when you simply select
>" DateDiff('yyyy', #DateFormat(TD, "mm/dd/yyyy")#, Scd) " as a field
>name without using it as a filter?
>
>Thanks.
>
>-Sandy Vohra
>
>-----Original Message-----
>From: Nick G [mailto:[EMAIL PROTECTED]
>Sent: Friday, December 29, 2006 10:53 AM
>To: CF-Newbie
>Subject: CF Query Select Strings
>
>I am having problems figuring out what the difference between two sets
>of queries. This query works as I need it to however it is not
efficient
>for the overall purpose for my page:
>
><cfquery name="r1" datasource="DB">
>Select * From Employees
></cfquery>
>
><cfset TD="09/30/2006">
><cfloop query="r1">
><cfif DateDiff("yyyy", #r1.Scd#, #DateFormat(TD, "mm/dd/yyyy")#) EQ 20>
><cfoutput>#Name#, #Dob#, #Scd#<br />cfoutput>
></cfif>
></cfloop>
>
>This is the code I have come up with to make it more efficient but does
>not select any records. Also, if I change the operator from "= 20" to
>">= 20" it selects all the records in the database...why does that
>happen too?:
>
><cfset TD="09/30/2006">
><cfquery name="r1" datasource="DB">
>Select * From Employees Where DateDiff('yyyy', #DateFormat(TD,
>"mm/dd/yyyy")#, Scd) = 20
>
><cfloop query="r1">
><cfoutput>#Name#, #Dob#, #Scd#<br /></cfoutput>
></cfloop>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2385
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
