EasyCFM.COM ColdFusion Forums / MS Access Related Issues / Using DayOfWeek in query

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Using DayOfWeek in query

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

02-24-2010 @ 11:09 AM
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

I have an access database that has a column of dates that I would like to have converted to a number for the day. I can use the DayOfWeek(CalendarDate) in the cfoutput and get the numerical string of the date. However, If I try to use the DayOfWeek(CalendarDate)in a query, I get the following error:

Undefined function 'DayOfWeek' in expression. Below is the query I am trying to use:

<cfquery name="getEvent" datasource="#dsn#">
select *
from venues
order by DayOfWeek(CalendarDate), starttime asc

How can I can I convert the CalendarDate to a number of 1-7 and order by the number so my output will list the days in proper order?

Thanks for any help.

02-24-2010 @ 11:13 AM
Send P.M.
My Gravatar!
Powered by Gravatar
Posts: 773
Joined: Apr 2003

You'll need to use a database function in a query, not a
CF function.

depends on your database, this is for sql server:

order by DATEPART(dw,CalendarDate), starttime asc

Nathan Miller
NM Consulting

Website Designed and Developed by Pablo Varando.