Skip to main content
A
Anonymous
March 29, 2010
Question

How do I Query A Date and have it output in numerical order?

  • March 29, 2010
  • 1 reply
  • 605 views

I have a calendar that stores the date of an event in the column calendardate as mm/dd/yyyy. Additionally, the day is sotered in the column calenderday as Monday. I am trying to query the access database and have the events listed by the day starting on Monday. I have tried variuos forms of query, but the closest I have come to any form of order is:

  <cfquery name="getEvent" datasource="#dsn#">
select CalendarDay,calendardate,  eventname
from venues
group by CalendarDay,calendardate,  eventname
</cfquery>

Unfortunately, it listes the days alphabetically instead of numerically, ie: Friday, Monday, Saturday, etc. I have tried:

  <cfquery name="getEvent" datasource="#dsn#">
select CalendarDay,calendardate,  eventname
from venues
order by dayofweek(calendardate)
</cfquery>

I get the followinf error: "Undefined function 'dayofweek' in  expression"

How do I query my database so the ouput lists the days starting with Monday?

I have run out of solutions.

    This topic has been closed for replies.

    1 reply

    ilssac
    ilssac
    Inspiring
    March 29, 2010

    Store the date data as a date field that the database understands not as a string that humans understand.

    Since you have the date data stored as strings, you can only query and manipulate the data as strings.  I.E. the order is going to be sorted alphabetically, etc.

    Your solution is to change the datatype of that column to datetime.

    A
    Anonymous
    March 29, 2010

    I have both types stored in the database, I don't care how I query the data, I just need to know how to convert the date to a numerical string, 1 through 7,  with 2 being Monday or whichever number Monday converts to, in my query so I can output it properly. I am not able to accomplish this in my query. without getting an undefined function in expression.

    There are several dates in the calendarDate column, 10/27/2009, 12/01/2009, that converts to 3 for Tuesday when I output #dayofweek(calendardate)#. However I can't use this string in my query without the undefined error.

    How do I convert this string into something my query will accept? Am I making sense?

    D
    Dan_Bracuk
    Inspiring
    March 29, 2010

    msaccess has many date functions, possibly even one that will work in your situation.  google "msaccess date functions" to find them.

    If that doesn't work, ColdFusion's DayOfWeek function should.

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