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

Question

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 · Answer

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded