3 Replies Latest reply: Aug 26, 2013 10:32 AM by straffenp RSS

    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!

        • 1. Re: Querying Dates
          Steve Sommers Community Member

          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.

          • 2. Re: Querying Dates
            Adam Cameron. Community Member

            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

            • 3. Re: Querying Dates
              straffenp Community Member

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