Skip to main content
A
Anonymous
July 25, 2011
Question

determining how many Tuesdays there are in a month

  • July 25, 2011
  • 4 replies
  • 3695 views

I have a date/time column named date_entered. I am trying to query hom many Tuesdays form any given month that have been entered, such as determining how many Tuesdays are in the month of April.

I tried using

<CFQUERY NAME="update" datasource="#application.database#">
select date_entered
from quarterly
where Month(date_entered) = 4 and dayofweek(date_entered) = 3
</cfquery>

Then I was trying to use #update.recordcount# to give me the amount of Tuesdays for that month.

but I keep getting an error that date_entered is undefined.

Any ideas why?

    This topic has been closed for replies.

    4 replies

    Owainnorth
    Owainnorth
    Inspiring
    July 27, 2011

    I've not been following that closely either, but the only problem with the original query is that you were using the ColdFusion function rather than the database one. Most database platforms have in-built functions to do what you were after.

    I.e.

    SELECT

      person,

      count(*)

    FROM

      meetings

    etc etc

    WHERE

      MONTH(meeting_date) = x

    AND

      DAY_OF_WEEK(meeting_date) = x

    GROUP BY

      person

    D
    Dan_Bracuk
    Inspiring
    July 25, 2011

    You appear to be attempting to use ColdFusion functions as part of your sql.  You have to use database functions instead.

    Also, your query won't necessarily count the number of Tuesdays in that month.

    A
    Anonymous
    July 25, 2011

    So, how do I determine the amount of Tuesdays in a month?

    D
    Dan_Bracuk
    Inspiring
    July 26, 2011

    I'd do it strictly with ColdFusion date functions.  I'd start with creating a date for the 1st of the month.  Then I'd be looking at the DayOfWeek() for that date, and the DaysInMonth for that date.  You should be able to take it from there.

    A
    Anonymous
    July 25, 2011

    My bad, here is the error message:

    [Macromedia][SequeLink JDBC Driver][ODBC  Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function  'dayofweek' in expression.

    The error occurred in wwwroot\forms\test-date.cfm Line 2

    -
    -__cfSearching__-
    Inspiring
    July 25, 2011

    Post the full error message.

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