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

[Guest]

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.

[ilssac]

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.

[Guest]

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?

[Dan_Bracuk]

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.