Skip to main content
A
Anonymous
September 11, 2008
Question

DayOfWeek Query Help

  • September 11, 2008
  • 2 replies
  • 293 views
I am trying to get a query where (see code attached) we will get a recordcount based on which day of the week it is however my query bombs out as it can't do the dayofweek function on my column ct_time. When I try it it says ct_time variable undefined. I am using a MYSQL DB. Thoughts on how to make this work?

Overall I want to get a recordcount for each day of the week and be able to output it to a webpage, thanks in advance.
    This topic has been closed for replies.

    2 replies

    P
    paross1
    Participating Frequently
    September 11, 2008
    Per MySQL 5.1 reference...

    DAYOFWEEK(date)

    Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.

    mysql> SELECT DAYOFWEEK('1998-02-03');
    -> 3

    Your query is written as if you were attempting to use a ColdFusion function against a database column, so lose the # characters....

    <cfquery name="dailycount" datasource="123">
    SELECT ct_id, ct_time
    FROM contacts
    WHERE dayofweek(ct_time) = 1
    </cfquery>

    ...assuming, of course, that ct_time is a date datatype column in your database.
    Phil
    D
    Dan_Bracuk
    Inspiring
    September 11, 2008
    You'll need db function, not a cf function. The specifics vary from db to db, but a google search on "date functions yourdb" might help.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices