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
Looks like what you want is to always only show the last six monthes of data... right? If so you can use a <cfset> tag to set a variable that will always generate a date six monthes ago [COLOR="Blue"]<cfset last_six_monthes = dateadd("m", + -6, #now()#)>[/COLOR] <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 [COLOR="blue"]>= #last_six_monthes#[/COLOR] ORDER BY date_create DESC </cfquery> Code (markup): You would have to have the data stored in the database as date_time format. If not then you just need to re format your #last_six_monthes# var to what ever format you need to match your database column. check out these two links http://livedocs.adobe.com/coldfusion/6/CFML_Reference/functions-pt159.htm#1103264 http://livedocs.adobe.com/coldfusion/6/CFML_Reference/functions-pt09.htm#1098968 A new date format might look like this <cfset last_six_monthes = 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 [COLOR="blue"]>= #dateformat( last_six_monthes, 'dd-mmm-yyyy')#[/COLOR] ORDER BY date_create DESC </cfquery> Code (markup): You can use whatever mask you need.