Skip to main content
A
Anonymous
January 15, 2014
Answered

Is using current date and dateAdd the best way to show 30 days worth of records .

  • January 15, 2014
  • 2 replies
  • 2437 views

I want to display files from the day a viewer logs in, through the next 30 days. I tried the code below but it does not work. What am I doing wrong?

<cfoutput><cfset mydatetime=#now()#></cfoutput>

   

<cfquery name="getdevotional" datasource="#application.database#">

SELECT display_date, title

FROM mobile

where display_date BETWEEN (#DateFormat(Now(),'YYYY/MM/DD')#) AND (#DateFormat(DateAdd('d', 30, mydatetime),'YYYY/MM/DD')#)

</cfquery>

    This topic has been closed for replies.
    Correct answer Carl Von Stetten

    Alternatively, use CFQueryParam and get rid of the DateFormat() calls:

    <cfquery name="getdevotional" datasource="#application.database#">

    SELECT display_date, title

    FROM mobile

    where display_date BETWEEN <cfqueryparam value ="#mydatetime#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#DateAdd('d', 30, mydatetime)#" cfsqltype="cf_sql_date">

    </cfquery>

    -Carl V.

    2 replies

    Carl Von Stetten
    Carl Von StettenCorrect answer
    Legend
    January 15, 2014

    Alternatively, use CFQueryParam and get rid of the DateFormat() calls:

    <cfquery name="getdevotional" datasource="#application.database#">

    SELECT display_date, title

    FROM mobile

    where display_date BETWEEN <cfqueryparam value ="#mydatetime#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#DateAdd('d', 30, mydatetime)#" cfsqltype="cf_sql_date">

    </cfquery>

    -Carl V.

    A
    Anonymous
    January 16, 2014

    Hi Carl, I appreciate you taking time to answer my question. I tried using the above code, but it is still showing 2013 even though I set mydatetime as the current time. Any idea?

    Carl Von Stetten
    Carl Von Stetten
    Legend
    January 16, 2014

    Rick,

    Which code sample are you are trying?  Chris's or mine?

    -Carl V.

    C
    ColdFusionChris
    Participant
    January 15, 2014

    Hi Rick,

    Depending on what database platform you're using you could do something similar to the following (My example is using T-SQL for SQL Server 2012)

    SELECT     .[display_date], .[title]

    FROM        [mobile] AS

    WHERE      .[display_date] <= DATEADD(day,GETDATE(),30) AND

                      .[display_date] >= GETDATE()

    Hope this helps!

    Carl Von Stetten
    Carl Von Stetten
    Legend
    January 15, 2014

    Rick,

    Just to clarify what ColdFusionChris is doing there:  the DATEADD and GETDATE functions are SQL Server T-SQL functions.  They are executed as part of the query on the database server (which is why they aren't wrapped in pound signs).

    -Carl V.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices