Solved

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

Posted on 2014-03-15
8
329 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 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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 Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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