• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

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

Guest
Jan 15, 2014 Jan 15, 2014

Copy link to clipboard

Copied

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>

Views

2.0K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Guide , Jan 15, 2014 Jan 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.

Votes

Translate

Translate
New Here ,
Jan 15, 2014 Jan 15, 2014

Copy link to clipboard

Copied

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!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jan 15, 2014 Jan 15, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jan 15, 2014 Jan 15, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 16, 2014 Jan 16, 2014

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jan 16, 2014 Jan 16, 2014

Copy link to clipboard

Copied

Rick,

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

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 16, 2014 Jan 16, 2014

Copy link to clipboard

Copied

yours

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jan 16, 2014 Jan 16, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 16, 2014 Jan 16, 2014

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jan 16, 2014 Jan 16, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 16, 2014 Jan 16, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jan 16, 2014 Jan 16, 2014

Copy link to clipboard

Copied

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

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 17, 2014 Jan 17, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jan 17, 2014 Jan 17, 2014

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 17, 2014 Jan 17, 2014

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation