Re: Query of Queries date comparison
I think he is working with dates You're right, I didn't notice the CFMX form of the function. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284933 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries date comparison
You can try using data object instead of a string formatted as a date. I think the column in your query is of type string. Bye Gualtiero - Original Message - From: Mark Lewis [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, July 30, 2007 1:43 PM Subject: Query of Queries date comparison Hi, Wondered if someone could advise on this problem I am having. I have created a query as shown below... cfset event = QueryNew(date, name, destination, date, varchar, varchar) cfset newRow = QueryAddRow(event, 2) cfset temp = QuerySetCell(event, date, 07/28/2007, 1) cfset temp = QuerySetCell(event, name, trip to zoo, 1) cfset temp = QuerySetCell(event, destination, london, 1) cfset temp = QuerySetCell(event, date, 07/31/2007, 2) cfset temp = QuerySetCell(event, name, trip to zoo, 2) cfset temp = QuerySetCell(event, destination, london, 2) I would like to run a query on this query where it finds only the records where the date is greater than today. I have written the following... cfquery name=search dbtype=query SELECT * FROM event WHERE date #DateFormat(Now(),mm/dd/)# /cfquery However I get an error returned Query Of Queries syntax error. Encountered date. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, Does this mean I cannot use this greater than ( ) clause on a query of queries, I have searched for some documentation but couldnt find any. I f anyone knows where any could be posted on the web, or a solution to this problem that would be great. Thank you in advance Mark ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284786 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries date comparison
Date is possibly(probably) a reserved word? Even if it isn't, your test will fail when the year rolls over - you should test /mm/dd. And I think you might need apostrophes around today's formatted date? None of this is guaranteed, I am famous for being wrong. -Original Message- From: Mark Lewis [mailto:[EMAIL PROTECTED] Sent: Monday, July 30, 2007 7:43 AM To: CF-Talk Subject: Query of Queries date comparison Hi, Wondered if someone could advise on this problem I am having. I have created a query as shown below... cfset event = QueryNew(date, name, destination, date, varchar, varchar) cfset newRow = QueryAddRow(event, 2) cfset temp = QuerySetCell(event, date, 07/28/2007, 1) cfset temp = QuerySetCell(event, name, trip to zoo, 1) cfset temp = QuerySetCell(event, destination, london, 1) cfset temp = QuerySetCell(event, date, 07/31/2007, 2) cfset temp = QuerySetCell(event, name, trip to zoo, 2) cfset temp = QuerySetCell(event, destination, london, 2) I would like to run a query on this query where it finds only the records where the date is greater than today. I have written the following... cfquery name=search dbtype=query SELECT * FROM event WHERE date #DateFormat(Now(),mm/dd/)# /cfquery However I get an error returned Query Of Queries syntax error. Encountered date. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, Does this mean I cannot use this greater than ( ) clause on a query of queries, I have searched for some documentation but couldnt find any. I f anyone knows where any could be posted on the web, or a solution to this problem that would be great. Thank you in advance Mark ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284787 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries date comparison
Not too sure about this, but have you tried it with GT maybe also try changing the filed name from date to tripDate - in case it is a reserved word On 7/30/07, Mark Lewis [EMAIL PROTECTED] wrote: Hi, Wondered if someone could advise on this problem I am having. I have created a query as shown below... cfset event = QueryNew(date, name, destination, date, varchar, varchar) cfset newRow = QueryAddRow(event, 2) cfset temp = QuerySetCell(event, date, 07/28/2007, 1) cfset temp = QuerySetCell(event, name, trip to zoo, 1) cfset temp = QuerySetCell(event, destination, london, 1) cfset temp = QuerySetCell(event, date, 07/31/2007, 2) cfset temp = QuerySetCell(event, name, trip to zoo, 2) cfset temp = QuerySetCell(event, destination, london, 2) I would like to run a query on this query where it finds only the records where the date is greater than today. I have written the following... cfquery name=search dbtype=query SELECT * FROM event WHERE date #DateFormat(Now(),mm/dd/)# /cfquery However I get an error returned Query Of Queries syntax error. Encountered date. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, Does this mean I cannot use this greater than ( ) clause on a query of queries, I have searched for some documentation but couldnt find any. I f anyone knows where any could be posted on the web, or a solution to this problem that would be great. Thank you in advance Mark ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284791 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Queries date comparison
Mark, I think the problem is the date column. Date is a reserved word, I think, and must be wrapped in []. Also, I would use a query param as it takes care of the data type conversion. cfquery name=search dbtype=query SELECT * FROM event WHERE [date] cfqueryparam value=#DateFormat(Now(),mm/dd/)# cfsqltype=CF_SQL_TIMESTAMP / /cfquery .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Mark Lewis [mailto:[EMAIL PROTECTED] Sent: Monday, July 30, 2007 7:43 AM To: CF-Talk Subject: Query of Queries date comparison Hi, Wondered if someone could advise on this problem I am having. I have created a query as shown below... cfset event = QueryNew(date, name, destination, date, varchar, varchar) cfset newRow = QueryAddRow(event, 2) cfset temp = QuerySetCell(event, date, 07/28/2007, 1) cfset temp = QuerySetCell(event, name, trip to zoo, 1) cfset temp = QuerySetCell(event, destination, london, 1) cfset temp = QuerySetCell(event, date, 07/31/2007, 2) cfset temp = QuerySetCell(event, name, trip to zoo, 2) cfset temp = QuerySetCell(event, destination, london, 2) I would like to run a query on this query where it finds only the records where the date is greater than today. I have written the following... cfquery name=search dbtype=query SELECT * FROM event WHERE date #DateFormat(Now(),mm/dd/)# /cfquery However I get an error returned Query Of Queries syntax error. Encountered date. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, Does this mean I cannot use this greater than ( ) clause on a query of queries, I have searched for some documentation but couldnt find any. I f anyone knows where any could be posted on the web, or a solution to this problem that would be great. Thank you in advance Mark ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284790 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries date comparison
Gualtiero and Dave thanks for your help I have resolved the issue with both your tips. Thank you for your time again Mark Date is possibly(probably) a reserved word? Even if it isn't, your test will fail when the year rolls over - you should test /mm/dd. And I think you might need apostrophes around today's formatted date? None of this is guaranteed, I am famous for being wrong. ~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284795 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries date comparison
Oh sorry, I only just got everyone else's responses through, thank you to you guys for your time too. Much appreciated, Mark ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284796 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries date comparison
I think you need quotes around your date in the comparison in the SQL. But I always have trouble with QoQ syntax for some reason. --Ben Doom Mark Lewis wrote: Hi, Wondered if someone could advise on this problem I am having. I have created a query as shown below... cfset event = QueryNew(date, name, destination, date, varchar, varchar) cfset newRow = QueryAddRow(event, 2) cfset temp = QuerySetCell(event, date, 07/28/2007, 1) cfset temp = QuerySetCell(event, name, trip to zoo, 1) cfset temp = QuerySetCell(event, destination, london, 1) cfset temp = QuerySetCell(event, date, 07/31/2007, 2) cfset temp = QuerySetCell(event, name, trip to zoo, 2) cfset temp = QuerySetCell(event, destination, london, 2) I would like to run a query on this query where it finds only the records where the date is greater than today. I have written the following... cfquery name=search dbtype=query SELECT * FROM event WHERE date #DateFormat(Now(),mm/dd/)# /cfquery However I get an error returned Query Of Queries syntax error. Encountered date. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, Does this mean I cannot use this greater than ( ) clause on a query of queries, I have searched for some documentation but couldnt find any. I f anyone knows where any could be posted on the web, or a solution to this problem that would be great. Thank you in advance Mark ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284794 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries date comparison
I would like to run a query on this query where it finds only the records where the date is greater than today. You cannot compare dates if they are stored in American format. Use QuerySetCell(event, date, 2007/07/28, 1) Instead: always with mask /mm/dd Then WHERE date #DateFormat(Now(),/mm/dd)# (you can even forget about slashes) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284802 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries date comparison
and yes, since you are actually working with strings, not dates, you do need quotes in WHERE date '#DateFormat(Now(),/mm/dd)#' -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284803 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries date comparison
Claude, I think he is working with dates; I think the data-type for that column in Querynew() was date. .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Monday, July 30, 2007 10:48 AM To: CF-Talk Subject: Re: Query of Queries date comparison . and yes, since you are actually working with strings, not dates, you do need quotes in WHERE date '#DateFormat(Now(),/mm/dd)#' ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284826 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
