freelanceprogrammers.org Forum Index » Cold Fusion

View previous topic :: View next topic

SQL Query Help

Goto page 1, 2 Next

jatrix32 Posted: Tue Sep 10, 2002 6:57 pm

Joined: 10 Sep 2002

Posts: 4

SQL Query Help

Does anyone know how to create a query that will pull all records
between two dates? Here is what I have:

<CFQUERY NAME="Jan" DataSource="IR">
SELECT PAFCCNumber,PAFDate2,PAFType
FROM PAF_Table
WHERE PAF_Table.PAFDate2 BETWEEN `12/31/01` AND `02/01/02`
</CFQUERY>

tcleary@... Posted: Tue Sep 10, 2002 6:58 pm

Joined: 10 Sep 2002

Posts: 5

SQL Query Help

What`s your backend database?

Thanks,
Tom

____________
Thomas Cleary
Clearysoft LLC
+1.877.299.0149
cell: 240.409.2891
tcleary@...

www.clearysoft.com

----- Original Message -----
From: "Jason Michalak" <jatrix32@...>
To: <cold_fusion@yahoogroups.com>
Sent: Tuesday, September 10, 2002 9:57 AM
Subject: [cold_fusion] SQL Query Help

> Does anyone know how to create a query that will pull all records
> between two dates? Here is what I have:
>
> <CFQUERY NAME="Jan" DataSource="IR">
> SELECT PAFCCNumber,PAFDate2,PAFType
> FROM PAF_Table
> WHERE PAF_Table.PAFDate2 BETWEEN `12/31/01` AND `02/01/02`
> </CFQUERY>
>
>
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>

tcleary@... Posted: Tue Sep 10, 2002 7:00 pm

Joined: 10 Sep 2002

Posts: 5

SQL Query Help

This will work in Access:

(((TABLENAME.StartDate)<###DateFormat((Form.myDate_1), "MM/DD/YYYY")###)
AND ((TABLENAME.EndDate)>###DateFormat((Form.myDate_2), "MM/DD/YYYY")###))

----- Original Message -----
From: "Jason Michalak" <jatrix32@...>
To: <cold_fusion@yahoogroups.com>
Sent: Tuesday, September 10, 2002 9:57 AM
Subject: [cold_fusion] SQL Query Help

> Does anyone know how to create a query that will pull all records
> between two dates? Here is what I have:
>
> <CFQUERY NAME="Jan" DataSource="IR">
> SELECT PAFCCNumber,PAFDate2,PAFType
> FROM PAF_Table
> WHERE PAF_Table.PAFDate2 BETWEEN `12/31/01` AND `02/01/02`
> </CFQUERY>
>
>
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>

bhhightower Posted: Tue Sep 10, 2002 7:05 pm

Joined: 10 Sep 2002

Posts: 9

SQL Query Help

<CFQUERY NAME="Jan" DataSource="IR">SELECT PAFCCNumber,PAFDate2,PAFTypeFROM PAF_TableWHERE PAF_Table.PAFDate2 BETWEEN #createODBCDate(variables.date_1)# AND #createODBCDate(variables.date_2)#</CFQUERY>

Brannon H. Hightower MCCFDSr. Web Application Developer512.784.4902hightower@...

-----Original Message-----From: Jason Michalak [mailto:jatrix32@...] Sent: Tuesday, September 10, 2002 8:58 AMTo: cold_fusion@yahoogroups.comSubject: [cold_fusion] SQL Query HelpDoes anyone know how to create a query that will pull all records between two dates? Here is what I have:<CFQUERY NAME="Jan" DataSource="IR">SELECT PAFCCNumber,PAFDate2,PAFTypeFROM PAF_TableWHERE PAF_Table.PAFDate2 BETWEEN `12/31/01` AND `02/01/02`</CFQUERY>Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

jatrix32 Posted: Tue Sep 10, 2002 7:24 pm

Joined: 10 Sep 2002

Posts: 4

SQL Query Help

Microsoft Access 97, also the Date that I am looking at is a Short Date Mask Thomas Cleary wrote:
What`s your backend database?Thanks,Tom____________Thomas ClearyClearysoft LLC+1.877.299.0149cell: 240.409.2891tcleary@...www.clearysoft.com----- Original Message -----From: "Jason Michalak" <jatrix32@...>To: <cold_fusion@yahoogroups.com>Sent: Tuesday, September 10, 2002 9:57 AMSubject: [cold_fusion] SQL Query Help> Does anyone know how to create a query that will pull all records> between two dates? Here is what I have:>> <CFQUERY NAME="Jan" DataSource="IR">> SELECT PAFCCNumber,PAFDate2,PAFType> FROM PAF_Table> WHERE PAF_Table.PAFDate2 BETWEEN `12/31/01` AND `02/01/02`> </CFQUERY>>>>>>> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/>>Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. I Am The OneYahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost

jatrix32 Posted: Tue Sep 10, 2002 7:27 pm

Joined: 10 Sep 2002

Posts: 4

SQL Query Help

How would I write this querying against the database. There is no input from the user. I am basically writing a query that will give me all products between the 2 dates I code in the query.
Thomas Cleary wrote:
This will work in Access: (((TABLENAME.StartDate)<###DateFormat((Form.myDate_1), "MM/DD/YYYY")###) AND ((TABLENAME.EndDate)>###DateFormat((Form.myDate_2), "MM/DD/YYYY")###))----- Original Message -----From: "Jason Michalak" <jatrix32@...>To: <cold_fusion@yahoogroups.com>Sent: Tuesday, September 10, 2002 9:57 AMSubject: [cold_fusion] SQL Query Help> Does anyone know how to create a query that will pull all records> between two dates? Here is what I have:>> <CFQUERY NAME="Jan" DataSource="IR">> SELECT PAFCCNumber,PAFDate2,PAFType> FROM PAF_Table> WHERE PAF_Table.PAFDate2 BETWEEN `12/31/01` AND `02/01/02`> </CFQUERY>>>>>>> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/>>Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. I Am The OneYahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost

jatrix32 Posted: Tue Sep 10, 2002 7:27 pm

Joined: 10 Sep 2002

Posts: 4

SQL Query Help

How would I write this querying against the database. There is no input from the user. I am basically writing a query that will give me all products between the 2 dates I code in the query.
Brannon Hightower wrote:

<CFQUERY NAME="Jan" DataSource="IR">SELECT PAFCCNumber,PAFDate2,PAFTypeFROM PAF_TableWHERE PAF_Table.PAFDate2 BETWEEN #createODBCDate(variables.date_1)# AND #createODBCDate(variables.date_2)#</CFQUERY>

Brannon H. Hightower MCCFDSr. Web Application Developer512.784.4902hightower@...

-----Original Message-----From: Jason Michalak [mailto:jatrix32@...] Sent: Tuesday, September 10, 2002 8:58 AMTo: cold_fusion@yahoogroups.comSubject: [cold_fusion] SQL Query HelpDoes anyone know how to create a query that will pull all records between two dates? Here is what I have:<CFQUERY NAME="Jan" DataSource="IR">SELECT PAFCCNumber,PAFDate2,PAFTypeFROM PAF_TableWHERE PAF_Table.PAFDate2 BETWEEN `12/31/01` AND `02/01/02`</CFQUERY>Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. I Am The OneYahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost

tcleary@... Posted: Tue Sep 10, 2002 7:30 pm

Joined: 10 Sep 2002

Posts: 5

SQL Query Help

<CFQUERY NAME="Jan" DataSource="IR">SELECT PAFCCNumber,PAFDate2,PAFTypeFROM PAF_TableWHERE
(((PAF_Table.PAFDate2)<###DateFormat((Form.VARIABLE_1), "MM/DD/YYYY")###) AND ((PAF_Table.PAFDate2)>###DateFormat((Form.VARIABLE_2), "MM/DD/YYYY")###))
</CFQUERY>

----- Original Message -----
From: Jason Michalak
To: cold_fusion@yahoogroups.com
Sent: Tuesday, September 10, 2002 10:27 AM
Subject: Re: [cold_fusion] SQL Query Help

How would I write this querying against the database. There is no input from the user. I am basically writing a query that will give me all products between the 2 dates I code in the query. Thomas Cleary wrote: This will work in Access: (((TABLENAME.StartDate)<###DateFormat((Form.myDate_1), "MM/DD/YYYY")###) AND ((TABLENAME.EndDate)>###DateFormat((Form.myDate_2), "MM/DD/YYYY")###))----- Original Message -----From: "Jason Michalak" <jatrix32@...>To: <cold_fusion@yahoogroups.com>Sent: Tuesday, September 10, 2002 9:57 AMSubject: [cold_fusion] SQL Query Help> Does anyone know how to create a query that will pull all records> between two dates? Here is what I have:>> <CFQUERY NAME="Jan" DataSource="IR">> SELECT PAFCCNumber,PAFDate2,PAFType> FROM PAF_Table> WHERE PAF_Table.PAFDate2 BETWEEN `12/31/01` AND `02/01/02`> </CFQUERY>>>>>>> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/>>Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. I Am The One

Yahoo! - We Remember9-11: A tribute to the more than 3,000 lives lost Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

bhhightower Posted: Tue Sep 10, 2002 7:47 pm

Joined: 10 Sep 2002

Posts: 9

SQL Query Help

<CFQUERY NAME="Jan" DataSource="IR">SELECT PAFCCNumber,PAFDate2,PAFTypeFROM PAF_TableWHERE PAF_Table.PAFDate2 BETWEEN #createODBCDate(mm/dd/yyyy)# AND #createODBCDate(mm/dd/yyyy)#</CFQUERY>

Brannon H. Hightower MCCFDSr. Web Application Developer512.784.4902hightower@...

-----Original Message-----From: Jason Michalak [mailto:jatrix32@...] Sent: Tuesday, September 10, 2002 9:27 AMTo: cold_fusion@yahoogroups.comSubject: RE: [cold_fusion] SQL Query Help
How would I write this querying against the database. There is no input from the user. I am basically writing a query that will give me all products between the 2 dates I code in the query. Brannon Hightower wrote:
<CFQUERY NAME="Jan" DataSource="IR">SELECT PAFCCNumber,PAFDate2,PAFTypeFROM PAF_TableWHERE PAF_Table.PAFDate2 BETWEEN #createODBCDate(variables.date_1)# AND #createODBCDate(variables.date_2)#</CFQUERY>

Brannon H. Hightower MCCFDSr. Web Application Developer512.784.4902hightower@...

-----Original Message-----From: Jason Michalak [mailto:jatrix32@...] Sent: Tuesday, September 10, 2002 8:58 AMTo: cold_fusion@yahoogroups.comSubject: [cold_fusion] SQL Query HelpDoes anyone know how to create a query that will pull all records between two dates? Here is what I have:<CFQUERY NAME="Jan" DataSource="IR">SELECT PAFCCNumber,PAFDate2,PAFTypeFROM PAF_TableWHERE PAF_Table.PAFDate2 BETWEEN `12/31/01` AND `02/01/02`</CFQUERY>Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. I Am The One

Yahoo! - We Remember9-11: A tribute to the more than 3,000 lives lost Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

Chrystalheart Posted: Tue Apr 05, 2005 5:21 am

Joined: 05 Apr 2005

Posts: 37

SQL Query Help

I have these 3 queries that are all similar. The first 2 work, but
the second doesn`t. I also tried > < operators, but it also didn`t
work. :-( Help, anyone? Thx!! Jen

<cfquery name="colA" datasource="#ds">
SELECT ID from people
where (Entered_Program BETWEEN #CreateODBCDate(start_yr1)#
AND #CreateODBCDate(end_yr1)#)
AND HID=#dist_site.ID#
</cfquery>
<cfquery name="colB" datasource="#ds#">
SELECT ID from people
where (Graduation BETWEEN #CreateODBCDate(start_yr1)#
AND #CreateODBCDate(end_yr1)#)
AND HID=#dist_site.ID#
</cfquery>
<cfquery name="colC" datasource="#ds#">
SELECT ID from people
where Entered_Program GT #CreateODBCDate(start_yr1)#
AND Graduation LT #CreateODBCDate(end_yr1)#
AND HID=#dist_site.ID#
</cfquery>

Collectonian Posted: Tue Apr 05, 2005 5:24 am

Joined: 05 Apr 2005

Posts: 2

SQL Query Help

If its an Access database, try putting the dates in pound signs (doubled up
signs), like
this:

<cfquery name="colC" datasource="#ds#">
SELECT ID from people
WHERE Entered_Program > ###CreateODBCDate(start_yr1)###
AND Graduation < ###CreateODBCDate(end_yr1)###
AND HID=#dist_site.ID#
</cfquery>

Summer S. Wilson, Collectonian on Yahoo! Messenger
Programmer/Analyst for Texas Cooperative Extension
Webmaster, An Eclectic World (http://eclectic-world.com)
Editor, EZ Learn Photography (http://ezlearnphotography.com)
Webmaster, Brazos Valley Macromedia Users Group (http://bvmmug.tamu.edu)
----- Original Message -----
From: "Jennifer" <chrystalheart@...>
To: <cold_fusion@yahoogroups.com>
Sent: Monday, April 04, 2005 7:21 PM
Subject: [cold_fusion] SQL Query Help

:
:
: I have these 3 queries that are all similar. The first 2 work, but
: the second doesn`t. I also tried > < operators, but it also didn`t
: work. :-( Help, anyone? Thx!! Jen
:
: <cfquery name="colA" datasource="#ds">
: SELECT ID from people
: where (Entered_Program BETWEEN #CreateODBCDate(start_yr1)#
: AND #CreateODBCDate(end_yr1)#)
: AND HID=#dist_site.ID#
: </cfquery>
: <cfquery name="colB" datasource="#ds#">
: SELECT ID from people
: where (Graduation BETWEEN #CreateODBCDate(start_yr1)#
: AND #CreateODBCDate(end_yr1)#)
: AND HID=#dist_site.ID#
: </cfquery>
: <cfquery name="colC" datasource="#ds#">
: SELECT ID from people
: where Entered_Program GT #CreateODBCDate(start_yr1)#
: AND Graduation LT #CreateODBCDate(end_yr1)#
: AND HID=#dist_site.ID#
: </cfquery>
:
:
:
:
:
:
:
: Yahoo! Groups Links
:
:
:
:
:
:
:

Chrystalheart Posted: Tue Apr 05, 2005 5:34 am

Joined: 05 Apr 2005

Posts: 37

SQL Query Help

That didn`t work. I got:

Syntax error (missing operator) in query expression `Entered_Program >
##12/31/2004## AND Graduation < ##1/1/2006## AND HID=50`.

Jen

Collectonian Posted: Tue Apr 05, 2005 5:49 am

Joined: 05 Apr 2005

Posts: 2

SQL Query Help

Make sure you date format as yyyy-mm-dd

Summer S. Wilson, Collectonian on Yahoo! Messenger
Programmer/Analyst for Texas Cooperative Extension
Webmaster, An Eclectic World (http://eclectic-world.com)
Editor, EZ Learn Photography (http://ezlearnphotography.com)
Webmaster, Brazos Valley Macromedia Users Group (http://bvmmug.tamu.edu)
----- Original Message -----
From: "Jennifer" <chrystalheart@...>
To: <cold_fusion@yahoogroups.com>
Sent: Monday, April 04, 2005 7:34 PM
Subject: [cold_fusion] Re: SQL Query Help

:
:
: That didn`t work. I got:
:
: Syntax error (missing operator) in query expression `Entered_Program >
: ##12/31/2004## AND Graduation < ##1/1/2006## AND HID=50`.
:
:
: Jen
:
:
:
:
:
:
: Yahoo! Groups Links
:
:
:
:
:
:
:

Chrystalheart Posted: Tue Apr 05, 2005 5:54 am

Joined: 05 Apr 2005

Posts: 37

SQL Query Help

Argh. Ok. I did that. Here is the improved code and the lines
above, where I format the date. Then the most recent error message.

<cfset start_yr1=#start_yr#+#q#-1>
<cfset start_yr1="12/31/#start_yr1#">
<cfset start_yr1="#DateFormat(start_yr1,"yyyy/mm/dd")#">
<cfset end_yr1=#start_yr#+#q#+1>
<cfset end_yr1="1/1/#end_yr1#">
<cfset end_yr1="#DateFormat(end_yr1,"yyyy/mm/dd")#">

<cfquery name="colA" datasource="#ds#">
SELECT ID from people
where (Entered_Program BETWEEN #CreateODBCDate(start_yr1)#
AND #CreateODBCDate(end_yr1)#)
AND HID=#dist_site.ID#
</cfquery>
<cfquery name="colB" datasource="#ds#">
SELECT ID from people
where (Graduation BETWEEN #CreateODBCDate(start_yr1)#
AND #CreateODBCDate(end_yr1)#)
AND HID=#dist_site.ID#
</cfquery>
<cfquery name="colC" datasource="#ds#">
SELECT ID from people
where Entered_Program > ###CreateODBCDate(start_yr1)###
AND Graduation < ###CreateODBCDate(end_yr1)###
AND HID=#dist_site.ID#
</cfquery>

Syntax error (missing operator) in query expression `Entered_Program
> ##12/31/2004## AND Graduation < ##1/1/2006## AND HID=50`.

SQL = "SELECT ID from people where Entered_Program > #{d `2004-12-
31`}# AND Graduation < #{d `2006-01-01`}# AND HID=50"

Chrystalheart Posted: Tue Apr 05, 2005 5:57 am

Joined: 05 Apr 2005

Posts: 37

SQL Query Help

Then I saw it was dashes, not slashes, and I changed those, but got
the same error.

Jen

Goto page 1, 2 Next

freelanceprogrammers.org Forum Index » Cold Fusion

All times are GMT
Page 1 of 2

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Freelace Website Designer - Customer web design and software building.
Booking Calendar - reservation calendar script
Land Surveying - total station instruments and equipments
China Wholesale - Electronics Products
Character Studio - Tutorials and Help