[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-03-15
8
Medium Priority
?
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 1332 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 668 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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
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 1332 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
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 this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

650 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