Skip to main content
S
straffenp
Known Participant
August 24, 2013
Answered

Querying Dates

  • August 24, 2013
  • 2 replies
  • 803 views

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!

    This topic has been closed for replies.
    Correct answer Steve Sommers

    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 replies

    A
    Adam Cameron.
    Inspiring
    August 26, 2013

    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

    S
    straffenpAuthor
    Known Participant
    August 26, 2013

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

    S
    Steve SommersCorrect answer
    Legend
    August 26, 2013

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices