Re: Where Date Statement in MySQL
Tried multiple combinations of createOBDCdate() and CFQUERYPARAM with CF_SQL_VARCHAR and it still does not work...I am missing something elementary...removed the between clause and replaced it with 2 statements = and = cfset startdate=#DateFormat(rsGetCustOrders.order_Date,'-mm-dd')# cfset enddate=#DateFormat(DateAdd(d,90,rsGetCustOrders.order_Date),'-mm-dd')# cfquery name=Recordset1 datasource=#request.dsn# SELECT * FROM tbl_picks Where LEFT(tbl_picks.Pick_SKU,4) != and tbl_picks.Pick_SKU != FREE and and (LEFT(tbl_picks.Pick_SKU,10) = #createODBCDate(startdate)# and LEFT(tbl_picks.Pick_SKU,10) = #createODBCDate(enddate)#) /cfquery The Pick_SKU the first 10 char are the DATE 2006-11-03 followed by the event and it is a string of up to 16 characters. Someone mentioned using Convert() on the sql side and I am wondering if you can give me an example...since everytime I tried it gave me an error. Any other ideas? Cornel ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259215 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Where Date Statement in MySQL
Try changing the != in your query to -Original Message- From: Cornel Ivanescu [mailto:[EMAIL PROTECTED] Sent: Sunday, November 05, 2006 7:36 AM To: CF-Talk Subject: Re: Where Date Statement in MySQL Tried multiple combinations of createOBDCdate() and CFQUERYPARAM with CF_SQL_VARCHAR and it still does not work...I am missing something elementary...removed the between clause and replaced it with 2 statements = and = cfset startdate=#DateFormat(rsGetCustOrders.order_Date,'-mm-dd')# cfset enddate=#DateFormat(DateAdd(d,90,rsGetCustOrders.order_Date),'-mm- dd')# cfquery name=Recordset1 datasource=#request.dsn# SELECT * FROM tbl_picks Where LEFT(tbl_picks.Pick_SKU,4) != and tbl_picks.Pick_SKU != FREE and and (LEFT(tbl_picks.Pick_SKU,10) = #createODBCDate(startdate)# and LEFT(tbl_picks.Pick_SKU,10) = #createODBCDate(enddate)#) /cfquery The Pick_SKU the first 10 char are the DATE 2006-11-03 followed by the event and it is a string of up to 16 characters. Someone mentioned using Convert() on the sql side and I am wondering if you can give me an example...since everytime I tried it gave me an error. Any other ideas? Cornel ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259216 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Where Date Statement in MySQL
My bad. That works in SQL Server I guess. -Original Message- From: Patrick Thomlinson [mailto:[EMAIL PROTECTED] Sent: Sunday, November 05, 2006 8:53 AM To: CF-Talk Subject: RE: Where Date Statement in MySQL Try changing the != in your query to -Original Message- From: Cornel Ivanescu [mailto:[EMAIL PROTECTED] Sent: Sunday, November 05, 2006 7:36 AM To: CF-Talk Subject: Re: Where Date Statement in MySQL Tried multiple combinations of createOBDCdate() and CFQUERYPARAM with CF_SQL_VARCHAR and it still does not work...I am missing something elementary...removed the between clause and replaced it with 2 statements = and = cfset startdate=#DateFormat(rsGetCustOrders.order_Date,'-mm-dd')# cfset enddate=#DateFormat(DateAdd(d,90,rsGetCustOrders.order_Date),'-mm- dd')# cfquery name=Recordset1 datasource=#request.dsn# SELECT * FROM tbl_picks Where LEFT(tbl_picks.Pick_SKU,4) != and tbl_picks.Pick_SKU != FREE and and (LEFT(tbl_picks.Pick_SKU,10) = #createODBCDate(startdate)# and LEFT(tbl_picks.Pick_SKU,10) = #createODBCDate(enddate)#) /cfquery The Pick_SKU the first 10 char are the DATE 2006-11-03 followed by the event and it is a string of up to 16 characters. Someone mentioned using Convert() on the sql side and I am wondering if you can give me an example...since everytime I tried it gave me an error. Any other ideas? Cornel ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259217 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Where Date Statement in MySQL
The != work fine that line always worked... the last where clause line is the culprit... Cornel ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259219 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Where Date Statement in MySQL
That explains why I could not find anything in MySQL to convert to date using convert() ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259220 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Where Date Statement in MySQL
Hi Guys, I am running into a problem that I have tried to solve for 3 hours now with no avail...I have the following statements and query: cfset startdate=#DateFormat(rsGetCustOrders.order_Date,'-mm-dd')# cfset enddate=#DateFormat(DateAdd(d,90,rsGetCustOrders.order_Date),'-mm-dd')# cfquery name=Recordset1 datasource=#request.dsn# SELECT * FROM tbl_picks Where LEFT(tbl_picks.Pick_SKU,4) != and tbl_picks.Pick_SKU != FREE and Left(tblpicks.Pick_SKU,10) between #startdate# and #enddate# /cfquery The query works without the last where clause and Left(tblpicks.Pick_SKU,10) between #startdate# and #enddate#; in the Pick_SKU the first 10 char are the DATE 2006-11-03 Thanks, Cornel ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259168 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Where Date Statement in MySQL
First, use CFQUERYPARAM with CF_SQL_VARCHAR as the type. There are various security/performance reasons, but it will also prevent MySQL from doing an implict conversion to a date. I don't know if it's happening, but it might be. Also consider that you're doing string comparisons, not date comparisons, so things might not be quite as you'd expect. cheers, barneyb On 11/4/06, Cornel Ivanescu [EMAIL PROTECTED] wrote: Hi Guys, I am running into a problem that I have tried to solve for 3 hours now with no avail...I have the following statements and query: cfset startdate=#DateFormat(rsGetCustOrders.order_Date,'-mm-dd')# cfset enddate=#DateFormat(DateAdd(d,90,rsGetCustOrders.order_Date),'-mm-dd')# cfquery name=Recordset1 datasource=#request.dsn# SELECT * FROM tbl_picks Where LEFT(tbl_picks.Pick_SKU,4) != and tbl_picks.Pick_SKU != FREE and Left(tblpicks.Pick_SKU,10) between #startdate# and #enddate# /cfquery The query works without the last where clause and Left(tblpicks.Pick_SKU,10) between #startdate# and #enddate#; in the Pick_SKU the first 10 char are the DATE 2006-11-03 Thanks, Cornel ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259170 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Where Date Statement in MySQL
First, use CFQUERYPARAM with CF_SQL_VARCHAR as the type. There are various security/performance reasons, but it will also prevent MySQL from doing an implict conversion to a date. I don't know if it's happening, but it might be. Also consider that you're doing string comparisons, not date comparisons, so things might not be quite as you'd expect. cheers, barneyb On 11/4/06, Cornel Ivanescu [EMAIL PROTECTED] wrote: ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259173 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Where Date Statement in MySQL
I actually got it to stop giving me an error by putting the first Left stateement in between quotes, however it does not compare them as dates but strings...I saw that once the error stopped showing up... How do I change it so MySQL does compare them as dates? as that was the original intent. Cornel First, use CFQUERYPARAM with CF_SQL_VARCHAR as the type. There are various security/performance reasons, but it will also prevent MySQL from doing an implict conversion to a date. I don't know if it's happening, but it might be. Also consider that you're doing string comparisons, not date comparisons, so things might not be quite as you'd expect. cheers, barneyb On 11/4/06, Cornel Ivanescu [EMAIL PROTECTED] wrote: ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259174 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Where Date Statement in MySQL
Convert them to dates. On the SQL side use the convert() function, on the CF side use the CreateODBCDate() fucntion. Russ -Original Message- From: Cornel Ivanescu [mailto:[EMAIL PROTECTED] Sent: 04 November 2006 18:40 To: CF-Talk Subject: Re: Where Date Statement in MySQL I actually got it to stop giving me an error by putting the first Left stateement in between quotes, however it does not compare them as dates but strings...I saw that once the error stopped showing up... How do I change it so MySQL does compare them as dates? as that was the original intent. Cornel First, use CFQUERYPARAM with CF_SQL_VARCHAR as the type. There are various security/performance reasons, but it will also prevent MySQL from doing an implict conversion to a date. I don't know if it's happening, but it might be. Also consider that you're doing string comparisons, not date comparisons, so things might not be quite as you'd expect. cheers, barneyb On 11/4/06, Cornel Ivanescu [EMAIL PROTECTED] wrote: ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259175 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Where Date Statement in MySQL
First, use CFQUERYPARAM with CF_SQL_VARCHAR as the type. There are various security/performance reasons, but it will also prevent MySQL from doing an implict conversion to a date. I don't know if it's happening, but it might be. Also consider that you're doing string comparisons, not date comparisons, so things might not be quite as you'd expect. cheers, barneyb On 11/4/06, Cornel Ivanescu [EMAIL PROTECTED] wrote: ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259176 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Where Date Statement in MySQL
Ok... I got it to work comparing it as a string (you were right)... which is not the intended action.I was missing the quotes around the left statement - putting them in got it working to compare... Now how do i compare it as a date? I have no way of changing the field to date type so I have to do it in the where statement somehow... Cornel ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259178 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
