Solved

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

Posted on 2014-03-15
8
328 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
ID: 39931862
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
ID: 39931909
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
ID: 39931942
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 39

Expert Comment

by:gdemaria
ID: 39931977
That's awesome Eric, thanks
0
 
LVL 52

Expert Comment

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

Author Comment

by:Eric Bourland
ID: 39933020
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
ID: 39933262
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
ID: 39933275
>>> 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Having an issue with NumberFormat 7 61
Align a <div> to a table row 3 84
ColdFusion 10 Error 2 60
Coldfusion - rename files in a folder 4 22
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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