Skip navigation
straffenp
Currently Being Moderated

Querying Dates

Aug 24, 2013 2:04 PM

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!

 
Replies
  • Currently Being Moderated
    Aug 26, 2013 8:26 AM   in reply to straffenp

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 26, 2013 8:41 AM   in reply to straffenp

    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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points