Copy link to clipboard
Copied
Hi all,
I am attempting to output some calendar dates from a database. I have a field called StartDate in the table formatted as mm/dd/yyyy. I'd like to output "future dates" for each month, so I've written this query:
<cfquery name="January" datasource="dsn">
SELECT *
FROM SITE:Calendar
WHERE DatePart('m', [StartDate]) = 1 and StartDate >= #Today# and Archive = 0
ORDER BY StartDate ASC, StartTime ASC
</cfquery>
FYI: #Today# is formatted as mm/dd/yyyy
This query works, but it also returns passed dates for the month. Question 1 is, why isn't the above query eliminating dates that have passed for the month of January? Is there a way to do this?
At another spot on the page, I'd like to output all dates less than #Today# that have passed for the current calendar year, so I've written this query:
<cfquery name="Past" datasource="dsn">
SELECT *
FROM SITE:Calendar
WHERE DatePart('yyyy', [StartDate]) = #Dateformat(Today, 'yyyy')# and StartDate < #Today# and Archive = 0
ORDER BY StartDate ASC, StartTime ASC
</cfquery>
This query returns 0 results, but there are quite a few records that should be counted. Question 2 is, can someone explain why this isn't working?
I appreciate in advance the help!
My guess is a format descrepancy. You should use the cfqueryparam tag or the createODBCDate() function to pass in your date into the query.
<cfquery name="January" datasource="dsn">
SELECT *
FROM SITE:Calendar
WHERE
DatePart('m', [StartDate]) = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" />
and StartDate >= <cfqueryparam value="#Today#" cfsqltype="CF_SQL_DATE" />
and Archive = <cfqueryparam value="0" cfsqltype="CF_SQL_INTEGER" />
ORDER BY StartDate ASC, StartTime ASC
</cf
...Copy link to clipboard
Copied
My guess is a format descrepancy. You should use the cfqueryparam tag or the createODBCDate() function to pass in your date into the query.
<cfquery name="January" datasource="dsn">
SELECT *
FROM SITE:Calendar
WHERE
DatePart('m', [StartDate]) = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" />
and StartDate >= <cfqueryparam value="#Today#" cfsqltype="CF_SQL_DATE" />
and Archive = <cfqueryparam value="0" cfsqltype="CF_SQL_INTEGER" />
ORDER BY StartDate ASC, StartTime ASC
</cfquery>
Hope this helps.
Copy link to clipboard
Copied
Can you please confirm the data type of the StartDate column. When you say it's "formatted as mm/dd/yyyy", is that just what you're seeing on the screen in front of you, or have you really got a varchar (etc) column in which you are storing date data as poorly-formatted strings?
--
Adam
Copy link to clipboard
Copied
Thank you, Steve and Adam. Steve's suggestion solved the issue I was having.