Solved

Worked in Access, but not in MySQL

Posted on 2003-11-10
4
187 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Domain Name E-mail Address & Outlook Integration 3 42
VPN Dedicated IP address question 7 45
Forbidden errors 5 152
WEB Farm 6 76
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

830 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