Skip navigation
Currently Being Moderated

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

Jan 15, 2014 1:04 PM

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>

 
Replies
  • Currently Being Moderated
    Jan 15, 2014 1:21 PM   in reply to rickaclark54

    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     [m].[display_date], [m].[title]

    FROM        [mobile] AS [m]

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

                      [m].[display_date] >= GETDATE()

     

    Hope this helps!

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 15, 2014 2:17 PM   in reply to ColdFusionChris

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 15, 2014 2:22 PM   in reply to rickaclark54

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 16, 2014 9:02 AM   in reply to rickaclark54

    Rick,

     

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

     

    -Carl V.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 16, 2014 10:34 AM   in reply to rickaclark54

    Hmmm.  I just tried the same approach on a table in one of my databases, and got back 2014 results.  If you dump your "mydatetime" variable, what does it look like?

    -Carl V.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 16, 2014 11:31 AM   in reply to rickaclark54

    Uggghhh.  MSAccess SQL may require wrapping your dates in pound signs (#) (I don't have the ODBC services installed on my machine to test this out).  If it does require the pound signs, you have to double them up to escape them from being used as ColdFusion variable outputs.  So I think your query would look like this (again, I can't test this to verify):

     

    SELECT mob_id, display_date, title, scripture, body

    FROM devotional

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

     

    If that doesn't work, try this instead:

     

    SELECT mob_id, display_date, title, scripture, body

    FROM devotional

    WHERE display_date BETWEEN ###CreateODBCDate( mydatetime )### AND ###CreateODBCDate( DateAdd( 'd', 30, mydatetime ) )###

     

     

    -Carl V.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 16, 2014 4:22 PM   in reply to rickaclark54

    Is the "display_date" date column actually a Date/Time field in MSAccess?  Or is it a text field?

     

    -Carl V.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 17, 2014 2:51 AM   in reply to rickaclark54

    I would stick with the cfqueryparam that Carl suggested. It enables the comparison of like with like. I am thinking along the lines of

     

    SELECT mob_id, display_date, title, scripture, body

    FROM devotional

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

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 17, 2014 8:55 AM   in reply to rickaclark54

    Rick,

     

    Glad you got it working.  Anytime you can move a database off of MSAccess onto a more capable RDBMS, it's a good thing.

     

    Could you kindly mark whichever solution above worked for you as "correct" for future users who might have a similar question?


    Thanks,

    -Carl

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points