Querying Dates

[straffenp]

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!

[Steve Sommers · Accepted solution]

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.

[Adam Cameron.]

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

[straffenp]

Thank you, Steve and Adam.  Steve's suggestion solved the issue I was having.