Skip to main content
J
jstama
Participant
March 19, 2008
Answered

Listing items only from an specific range

  • March 19, 2008
  • 7 replies
  • 691 views
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
    This topic has been closed for replies.
    Correct answer jstama
    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

    7 replies

    A
    Anonymous
    March 21, 2008
    jstama,

    ALWAYS post your DB platform info when asking a db-related question.
    J
    jstamaAuthorCorrect answer
    Participant
    March 21, 2008
    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__-
    Inspiring
    March 21, 2008
    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.
    N
    Newsgroup_User
    Inspiring
    March 20, 2008
    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.

    D
    Dan_Bracuk
    Inspiring
    March 20, 2008
    it's a cached query. cfqueryparam does not play nice with those at all.
    O
    Oğuz Demirkapı
    Participating Frequently
    March 20, 2008
    "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. :)

    Astonished_protector15C3
    Astonished_protector15C3
    Participating Frequently
    March 20, 2008
    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__-
    Inspiring
    March 20, 2008
    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.
    D
    Dan_Bracuk
    Inspiring
    March 20, 2008
    If your db has date functions, and not all do, use them.
    N
    Newsgroup_User
    Inspiring
    March 20, 2008
    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/
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices