Solved

Worked in Access, but not in MySQL

Posted on 2003-11-10
4
185 Views
Last Modified: 2013-12-24
Hi

Can anyone tell me why this worked using Access, but doesn't when using MySQL?

<cfset currentDate = CreateODBCDate(Now())>

<cfquery name="bulletinBoard" datasource="intranet2">
SELECT *
FROM MAIN
WHERE (CATEGORY = 'Bulletin Board' AND SUBMITTED >= #currentDate-90#)
ORDER BY SUBMITTED DESC, ID DESC</cfquery>

This is supposed to return all of the entries within the database from the Bulletin Board in the last 90 days.

Cheers
Nick
0
Comment
Question by:nelliott
  • 3
4 Comments
 
LVL 11

Expert Comment

by:hart
ID: 9714011
try this
<cfset currentDate = CreateODBCDate(Now())>
<cfset submitteddate = dateadd('d',currentDate,-90)>

SELECT *
FROM MAIN
WHERE (CATEGORY = 'Bulletin Board' AND SUBMITTED >= #CreateOdbcDateTime(submitteddate)#)
ORDER BY SUBMITTED DESC, ID DESC


Regards
Hart

0
 
LVL 11

Accepted Solution

by:
hart earned 250 total points
ID: 9714038
much better way would be to use cfqueryparam

SELECT *
FROM MAIN
WHERE CATEGORY = <CFQUERYPARAM      CFSQLTYPE="cf_sql_varchar" VALUE="Bulletin Board">
            AND SUBMITTED >= <CFQUERYPARAM CFSQLTYPE="cf_sql_date" VALUE="#submitteddate#">
ORDER BY SUBMITTED DESC, ID DESC

Regards
Hart
0
 

Author Comment

by:nelliott
ID: 9714127
Thanks very much Hart.

Would you advise using cfqueryparam on every query?  
0
 
LVL 11

Expert Comment

by:hart
ID: 9714222
yes i would :-)

Regards
Hart
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now