Listing items only from an specific range

[jstama]

Hi,

I have a list of about 100 pages, each of them using date_create variable for the date they were entered in the database; these entries are from years 2005 to 2008. I need to list the ones from the last six months only, but without hardcoding specific values, so it automatically outputs the correct list. Could somebody help me modifying the code to accomplish this?

The code I'm using as of now is:

<cfquery name="disp_news_arti_curr" datasource="#Request.MainDSN#" CACHEDWITHIN="#CreateTimeSpan(1,0,0,0)#">
SELECT
id_news_arti,url,name_pub,date_create,author_pub,title
FROM
data_news_arti
WHERE
status = 1 AND date_create >= '01 Sep 2005'
ORDER BY
date_create DESC
</cfquery>

Thanks
jstama@hotmail.com

[Newsgroup_User]

look at coldfusion's date functions, specifically DateAdd(), and keep in
mind that you can add negative numbers,too...

also take care that you are comparing relevant parts of dates: i.e. if
you db table stores dates as date AND time, extract only date part from
it using your db's functions.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Dan_Bracuk]

If your db has date functions, and not all do, use them.

[Astonished_protector15C3]

Hi

Please use dateadd function instead of 01 Sep 2005 in your query

For eg:
<cfset strdate=dateadd('d',-6,now())>
<cfset strdate=dateformat(strdate,'mm/dd/yyyy')>

[-__cfSearching__-]

quote:

---

Originally posted by: V.K.R
<cfset strdate=dateadd('d',-6,now())>
<cfset strdate=dateformat(strdate,'mm/dd/yyyy')>

---

If you choose to use ColdFusion's date functions, convert the date string into a date object first. Take a look at the ParseDateTime function. Though I would use cfqueryparam myself.

[Oğuz Demirkapı]

"cfqueryparam" usage should resolve most of the date issues.

Of couse if you have the knowledge of using your DB platform functions, that would be nicer. :)

[Dan_Bracuk]

it's a cached query. cfqueryparam does not play nice with those at all.

[Newsgroup_User]

Dan Bracuk wrote:
> it's a cached query. cfqueryparam does not play nice with those at all.

Before CF8.

As of CF8 this long standing issue has finally been removed. At least
that is what I have been told.

[Guest]

jstama,

ALWAYS post your DB platform info when asking a db-related question.

[jstama · Accepted solution]

Thanks all for your help. I inherited this CF-based web site and have very little knowledge of this technology.

I was able to apply some of your feedback to modify the code. Here is the final copy:

<cfset last_six_months = dateadd("m", -6, #now()#)>

<cfquery name="disp_news_arti_curr" datasource="#Request.MainDSN#" CACHEDWITHIN="#CreateTimeSpan(1,0,0,0)#">
SELECT id_news_arti, url, name_pub, date_create, author_pub, title
FROM data_news_arti
WHERE status = 1 AND date_create >= #last_six_months#
ORDER BY date_create DESC
</cfquery>

Many thanks.
jstama

[-__cfSearching__-]

jstama wrote:
> <cfset last_six_months = dateadd("m", -6, #now()#)>

Read Azadi's comments and V.K.R's example again. They specifically mention proper handling of the time portion. Your code will create a date value that includes the current time. So the actual query will look something like this

WHERE status = 1 AND date_create >= {ts '2007-09-21 09:46:46'}

That is different than your original query and may accidentally omit some records due to the inclusion of the time portion.

Btw, you can also lose the pound # signs. The code will work either way, but they are superfluous.