Solved

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

Posted on 2014-03-15
8
325 Views
Last Modified: 2014-03-16
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
Comment
Question by:Eric Bourland
  • 3
  • 3
  • 2
8 Comments
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 167 total points
Comment Utility
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
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
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
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
That's awesome Eric, thanks
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Thanks, nicest thing I've heard all day :)
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
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
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility
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
 
LVL 3

Author Closing Comment

by:Eric Bourland
Comment Utility
>>> 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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

771 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

13 Experts available now in Live!

Get 1:1 Help Now