• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

date compare in query not functioning correctly

Hi all,
I have the following query:
<CFQUERY NAME="ExpiredArticles" DATASOURCE="#mylibrarydata#" username="#sqlusername#" password="#sqlpassword#">
SELECT articleid,title,articledate,requests,ahitcount FROM articles
WHERE (articles.articledate<(Now()-8)) AND articles.requests>0 AND articles.words>-1
ORDER BY articles.articledate;
</CFQUERY>


Basically what I'm doing is taking today's date and comparing it to an articledate within the the query. For some reason though it's outputting all the results from the db. Any idea why this is?

Thanks,
Rick
0
bluskyGuy
Asked:
bluskyGuy
  • 7
  • 4
  • 2
2 Solutions
 
mrichmonCommented:
Is it access? (if not then Now() is wrong)  

Usually you want to use datediff or dateadd instead of simple subtraction.

Try this:

<CFQUERY NAME="ExpiredArticles" DATASOURCE="#mylibrarydata#" username="#sqlusername#" password="#sqlpassword#">
SELECT articleid,title,articledate,requests,ahitcount FROM articles
WHERE (articles.articledate<#DateAdd("d", -8, Now()) AND articles.requests>0 AND articles.words>-1
ORDER BY articles.articledate;
</CFQUERY>
0
 
bluskyGuyAuthor Commented:
It's actually mysql.
0
 
bluskyGuyAuthor Commented:
I tried running your syntax and received an error...here's my query:

<CFQUERY NAME="ExpiredArticles" DATASOURCE="#mylibrarydata#" username="#sqlusername#" password="#sqlpassword#">
SELECT articleid,title,articledate,requests,ahitcount FROM articles
WHERE (articles.articledate<#DateAdd("d", -8, Now())# AND articles.requests >0 AND articles.words > -1
ORDER BY articles.articledate
</CFQUERY>
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
mrichmonCommented:
what error?  Probably you need to format the date correctly.

Use ' around date in MySQL I think - or use <cfqueryparam cfsqltype="cf_sql_timestamp" value="#DateAdd("d", -8, Now())#">
0
 
trailblazzyr55Commented:
try this

<cfset date = DateAdd("D", -8,(DateFormat(NOW(),"MM/DD/YYYY"))>


change where statement to this..


WHERE articledate = #CreateODBCDate(date)# AND requests > 0  AND words > -1


that should work for ya ;o)
~trail

0
 
trailblazzyr55Commented:
caught a slight error just cut and past this...

<cfset date = DateAdd("D", -8, DateFormat(NOW(),"MM/DD/YYYY"))>

<CFQUERY NAME="ExpiredArticles" DATASOURCE="#mylibrarydata#" username="#sqlusername#" password="#sqlpassword#">
SELECT articleid,title,articledate,requests,ahitcount FROM articles
WHERE articledate = #CreateODBCDate(date)# AND requests > 0  AND words > -1
ORDER BY articledate
</CFQUERY>

hope that helps ;o)
~trail

by the way if you're only selecting from one table you don't need to add the table name to the front of the column name, I made the changes in the query above. for outputing it it's different when outside the query or if you're using more the one table to query.
0
 
trailblazzyr55Commented:
oh forgot you had the less than instead of equals use this where statement...

WHERE articledate < #CreateODBCDate(date)# AND requests > 0  AND words > -1

also if you want to put it in cfqueryparam then do this...

above query:
<cfset date = DateAdd("D", -8, DateFormat(NOW(),"MM/DD/YYYY"))>

in query:
WHERE articledate < <cfqueryparam cfsqltype="cf_sql_date" value="#Date#"> AND
            requests > 0 AND
            words > -1

That's it
~trail

0
 
mrichmonCommented:
trailblazzyr55, With many MySQL drivers it *does* require the table name even if you are only selecting from one table.  A strange quirk that certain MySQL drivers have....
0
 
trailblazzyr55Commented:
really? I didn't know that about mysql, good to know ;o) I've really dealt mainly with sql server... sometimes access.

curious... I saw when you were using the cfqueryparam you added the cfsqltype="timestamp" instead of cfsqltype="cf_sql_date"

why would you do that?

~trail
0
 
trailblazzyr55Commented:
bluskyguy,

then this would be what you'd want..

<cfset date = DateAdd("D", -8, DateFormat(NOW(),"MM/DD/YYYY"))>

<CFQUERY NAME="ExpiredArticles" DATASOURCE="#mylibrarydata#" username="#sqlusername#" password="#sqlpassword#">
SELECT articleid,title,articledate,requests,ahitcount FROM articles
WHERE articles.articledate < #CreateODBCDate(articles.date)# AND articles.requests > 0  AND articles.words > -1
ORDER BY articles.articledate
</CFQUERY>

or

<cfset date = DateAdd("D", -8, DateFormat(NOW(),"MM/DD/YYYY"))>

<CFQUERY NAME="ExpiredArticles" DATASOURCE="#mylibrarydata#" username="#sqlusername#" password="#sqlpassword#">
SELECT articleid,title,articledate,requests,ahitcount FROM articles
WHERE articles.articledate < <cfqueryparam cfsqltype="cf_sql_date" value="#Date#"> AND
            articles.requests > 0 AND
            articles.words > -1
ORDER BY articles.articledate
</CFQUERY>

;o)
0
 
mrichmonCommented:
There is a bug that can happen with cf_sql_date that I was told useing cf_sql_timestamp avoided.

But it is probably best to start with date and then only use timestamp if there is an error
0
 
trailblazzyr55Commented:
waiting for feedback ;o)
0
 
trailblazzyr55Commented:
the post I made 05/13/2005 10:52PM PDT should answer the question, it would work with MySQL, the date functions are not outside the parameters, if bluskyGuy doesn't have any further questions or would like to keep this open I would say split you and I.. your post 05/13/2005 03:27PM PDT should also be a possible solution that would work...

~trail
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now