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>
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.
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
FROM [mobile] AS
WHERE
Hope this helps!
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.
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.
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?
Copy link to clipboard
Copied
Rick,
Which code sample are you are trying? Chris's or mine?
-Carl V.
Copy link to clipboard
Copied
yours
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.
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">
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.
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:
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.
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.
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.
Copy link to clipboard
Copied
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
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">