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

[Guest]

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>

[ColdFusionChris]

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]

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.

[Carl Von Stetten · Accepted solution]

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.

[Guest]

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]

Rick,

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

-Carl V.

[Guest]

yours

[Carl Von Stetten]

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.

[Guest]

mydatetime: {ts '2014-01-16 10:56:19'} I neglected to tell you that this database is older work that i did on ms access as opposed to mySQL. Does it matter?

Additionally, the query is also showing all records, not just the first 30.

Here is the line of code I used:

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">

[Carl Von Stetten]

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.

[Guest]

I could not get your codes to work. If I Just used the following code:

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

SELECT dev_id, display_date, title, scripture, body

FROM devotional

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

</cfquery>

When I output the display date in a list, I get 3 different sets in multiples of 30:

1. 01/17/2013 through 02/15/2013
2. 01/16/2014 through 02/15/2014
3. 01/16/2015 through 02/14/2015

So, it is adding the 30 days to the 16th, but does not display only the current year, but is displaying 30 days for the three years that are listed in the database.

[Carl Von Stetten]

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

-Carl V.

[Guest]

Gentlemen, I converted the access database to mySQL and made sure the display_date was a date field and all is working fine now. Thank you for all of your help and patience. BTW, using dateValue() gave me a function error.

[Carl Von Stetten]

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

[BKBK]

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">