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

query: show news items that are fewer than ninety days old

ColdFusion 9
MS SQL Server 2012

Hi. I'm trying to build a query that requests news items that are fewer than ninety days old.

I started with this:

<cfset CurrentDate = now()>

<cfquery name="getNews" datasource="#application.datasource#">
SELECT newsID, newsTitle, newsExcerpt, newsAuthor, NewsDate
FROM #REQUEST.NewsTable#
WHERE NewsDate GT CurrentDate <cfqueryparam cfsqltype="cf_sql_integer" value="-90">
ORDER BY NewsDate DESC
</cfquery>

... but that gets me an error, and obviously I need help with the WHERE clause.

I also tried:

WHERE DATEDIFF(DAY, GETDATE(), NewsDate) > 90

But this returns No Records!

I've been researching this some ... but figured someone here knows the answer. How would you do this?

Also should I use cfqueryparam, above? =)

Thank you for any advice.

Eric
0
Eric Bourland
Asked:
Eric Bourland
  • 3
  • 3
  • 2
3 Solutions
 
gdemariaCommented:
eric,

Of the choices you've shown this would be the correct format..
WHERE DATEDIFF(DAY, GETDATE(), NewsDate) > 90



try putting this in your select clause and see what number it returns

DATEDIFF(DAY, GETDATE(), NewsDate)


Perhaps you have to switch the dates, or maybe it is returning a negative number instead of a positive one?
0
 
_agx_Commented:
You can also do it this way, which is a little more index friendly than dateDiff

<cfset ninetyDaysAgo = dateAdd("d", -90, now())>
...
WHERE NewsDate  >= <cfqueryparam value="#ninetyDaysAgo#" cfsqltype="cf_sql_date">


(Edit)

> Perhaps you have to switch the dates

Definitely. Arranged that way, the result will only be > 0 if NewsDate is later than getDate().  ie a Future date.
0
 
Eric BourlandAuthor Commented:
I always smile -- actually, I cheer at my desk kinda loud -- when I post a question and you two post replies. Thank you. =)

I gotta go cook dinner -- but will be back later and try out these ideas and let you know what I get.

Hope you are both well. Have a great evening.

Eric
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
gdemariaCommented:
That's awesome Eric, thanks
0
 
_agx_Commented:
Thanks, nicest thing I've heard all day :)
0
 
Eric BourlandAuthor Commented:
Hey there. Just getting a chance to return to this. Here is what I found. First I did this:

<cfquery name="getNews" datasource="#application.datasource#">
SELECT newsID, newsTitle, newsExcerpt, newsAuthor, NewsDate, DATEDIFF(DAY, GETDATE(), NewsDate)
FROM #REQUEST.NewsTable#
ORDER BY NewsDate DESC
</cfquery>

Open in new window


and that simply returns all news records in the output.

gdemaria I ~think~ that is what you intended me to try out.

>>>Perhaps you have to switch the dates, or maybe it is returning a negative number instead of a positive one?

I was not sure what you meant by switching dates -- but I pressed on, and tried a number of different things, variations on:

<cfquery name="getNews" datasource="#application.datasource#">
SELECT newsID, newsTitle, newsExcerpt, newsAuthor, NewsDate, DATEDIFF(DAY, GETDATE(), NewsDate)
FROM #REQUEST.NewsTable#
WHERE DATEDIFF(DAY, GETDATE(), NewsDate) < 90
ORDER BY NewsDate DESC
</cfquery>

Open in new window


mainly just to see what would happen. Using < and >; using values other than 90 -- 9; 900; and so on. I also did reading on DateDiff: http://technet.microsoft.com/en-us/library/ms189794.aspx ... and this makes sense to me.

This started to make sense when I used negative values:

<cfquery name="getNews" datasource="#application.datasource#">
SELECT newsID, newsTitle, newsExcerpt, newsAuthor, NewsDate
FROM #REQUEST.NewsTable#
WHERE DATEDIFF(DAY, GETDATE(), NewsDate) > -9  (or -90 or -900 etc)
ORDER BY NewsDate DESC
</cfquery>

Open in new window


... this returns records as expected. This makes sense to me now and I think this solution works -- and I see why what I had before did not work.

Next I tried _agx_'s idea:

<cfset ninetyDaysAgo = dateAdd("d", -90, now())>

<cfquery name="getNews" datasource="#application.datasource#">
SELECT newsID, newsTitle, newsExcerpt, newsAuthor, NewsDate
FROM #REQUEST.NewsTable#
WHERE NewsDate >= <cfqueryparam value="#ninetyDaysAgo#" cfsqltype="cf_sql_date">
ORDER BY NewsDate DESC
</cfquery>

Open in new window


And this works perfectly well too. Which do you think is better to use? =)

Hope you're both doing well. Thank you as always.

Eric
0
 
gdemariaCommented:
I would use your last code sample from agx because there is no function to slow things down, just a direct date comparison.



 Btw, the purpose of having tried this..
SELECT newsID, newsTitle, newsExcerpt, newsAuthor, NewsDate, DATEDIFF(DAY, GETDATE(), NewsDate)
FROM #REQUEST.NewsTable#

was to look at the value returned by DATEDIFF so you could see that that the values returned by the function didn't match the way you were testing in the where clause, which I believe you discovered
0
 
Eric BourlandAuthor Commented:
>>> Btw, the purpose of having tried this..
SELECT newsID, newsTitle, newsExcerpt, newsAuthor, NewsDate, DATEDIFF(DAY, GETDATE(), NewsDate)
FROM #REQUEST.NewsTable#

was to look at the value returned by DATEDIFF so you could see that that the values returned by the function didn't match the way you were testing in the where clause, which I believe you discovered

This makes a lot of sense. Thank you, gdemaria and _agx_.

Take care and have a great evening.

Eric
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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