Solved

Worked in Access, but not in MySQL

Posted on 2003-11-10
4
184 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks very much Hart.

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

Expert Comment

by:hart
Comment Utility
yes i would :-)

Regards
Hart
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

9 Experts available now in Live!

Get 1:1 Help Now