RE: How to order by days of the week, but not alphabetically in this query

It seems you could make a related table holding an ID and day name and do a join to get both. John -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 5:03 PM To: CF-Talk Subject: RE: How to order by days of the week, but not

How to order by days of the week, but not alphabetically in this query

Hi, all... How do I get this query to order by days of the week according to the calendar, not alphabetically? CFQUERY Name=GetDays Datasource=#DSN# Select WS_WeekDay from weeklyschedule group by WS_WeekDay order by WS_WeekDay /CFQUERY I tried order by DayofWeek(WS_WeekDay) as a guess, but

RE: How to order by days of the week, but not alphabetically in this query

maybe give another column that is day of week... so that all wendesdays have day (4) (assuming starting Sunday)? tony -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 3:21 PM To: CF-Talk Subject: How to order by days of the week, but

Re: How to order by days of the week, but not alphabetically in this query

Assuming you have SQL Server (the synatax in Oracle would be different) Select myfield, datepart(dw,myDateField)as weekday from MyTable order by myDate Hints to make this happen 1) myDate should be a datetime field. If it is not then use SQL Server's CONVERT function as in

RE: How to order by days of the week, but not alphabetically in this query

Thanks for the reply, Tony. I was hoping to avoid another column. Is there any way in MySQL to convert the name of the weekday to a numeric value for ordering? If not, I guess I should have just used numeric values in the db instead of names... Rick -Original Message- From: Tony Weeg

Re: How to order by days of the week, but not alphabetically in this query

I hit the post message button by accident before adding the correct order by functionality Assuming you have SQL Server (the synatax in Oracle would be different) Select myfield, datepart(dw,myDateField)as weekday from MyTable order by datepart(dw,myDateField) Hints to make this happen 1)

RE: How to order by days of the week, but not alphabetically in this query

not too sure about mysql.im the dumb one for assuming sql server :)but im sure there is a mysql expert that can shed some light tony -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 3:39 PM To: CF-Talk Subject: RE: How to order by

RE: How to order by days of the week, but not alphabetically in this query

Is there any way in MySQL to convert the name of the weekday to a numeric value for ordering? http://www.mysql.com/documentation/mysql/bychapter/manual_Functions.html#Date_and_time_functions DATE_FORMAT(date,format) Formats the date value according to the format string. The following

RE: How to order by days of the week, but not alphabetically in this query

Hmmm...I even converted the column in the db table to tinyint and I've started using 0, 1, 2, etc. for days of the week (0=Sunday), but still can't find any way to change 0 to Sunday for display... Haven't found any way to do it in the MySQL docs...can't believe it's not possible to convert 0 to

RE: How to order by days of the week, but not alphabetically in this query

%wDay of the week (0=Sunday..6=Saturday) I saw that, Rob, and held out hope that it might work, but, as you quoted... Formats the date value it requires a date value...this is just a string...Monday, Tuesday, etc. Is there a way to use this that I'm missing? CFQUERY Name=GetDays

RE: How to order by days of the week, but not alphabetically in this query

cant you just check for the day...have the days in an array (session.daysArray) in the session scope...where you use 1-7 versus 0-6? since cf arrays use 1 as beginning, and in your presentation layer, decide what to show based on the value and then use the daysArray to determine what to show? 1-

RE: How to order by days of the week, but not alphabetically in this query

In MySQL (don't know about other systems), you can use DAYOFWEEK(date) or WEEKDAY(date).Neither one returns the correct integer start point, but fixing that is as simple as add/subtract and a modulo. http://www.mysql.com/doc/en/Date_and_time_functions.html Cheers, barneyb -Original

RE: How to order by days of the week, but not alphabetically in this query

it requires a date value...this is just a string...Monday, Tuesday, etc. Is there a way to use this that I'm missing? CFQUERY Name=GetDays Datasource=#DSN# Select WS_WeekDay from weeklyschedule group by WS_WeekDay order by WS_WeekDay /CFQUERY This might sound cheesy, but how

RE: How to order by days of the week, but not alphabetically in this query

I saw that, Rob, and held out hope that it might work, but, as you quoted... Formats the date value it requires a date value...this is just a string...Monday, Tuesday, etc. :-( - bogus Is there a way to use this that I'm missing? CFQUERY Name=GetDays Datasource=#DSN# Select

RE: How to order by days of the week, but not alphabetically in this query

So... Create a 1-dimensional array to hold the day names, match the number in the db to the row number in the array, then pull the name from the array for display?Is that correct? Boy, seems like there would be a straight-forward way to do this... Rick -Original Message- From: Tony

RE: How to order by days of the week, but not alphabetically in this query

yeah. there is probably, but a different db design may lend itself to this... tony -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 4:54 PM To: CF-Talk Subject: RE: How to order by days of the week, but not alphabetically in this

RE: How to order by days of the week, but not alphabetically in this query

Well... While I was waiting for replies, I went ahead and change the days of the week in the db field to 01_Monday 02_Tuesday etc. and used #ListLast(GetDays.WeekDay, '_')# for display. That way the order by in the query used the numbers for ordering, then I just display the day part. I'll just

RE: How to order by days of the week, but not alphabetically in this query

I actually considered that, but see my solution message and I think it works easier... Rick -Original Message- From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 4:49 PM To: CF-Talk Subject: RE: How to order by days of the week, but not alphabetically in

Re: How to order by days of the week, but not alphabetically in this query

I'm not sure about MySQL, but in Oracle you can do this easily, assuming I am understanding correctly what you need, maybe a similar solution is available for MySQL? CFQUERY Name=GetDays Datasource=#DSN# Select WS_WeekDay from weeklyschedule order by case when WS_WeekDay eq 'Sunday' then 1

Re: How to order by days of the week, but not alphabetically in this query

haha...using CF too long.Replace the 'eq' with '=' please. I'm not sure about MySQL, but in Oracle you can do this easily, assuming I am understanding correctly what you need, maybe a similar solution is available for MySQL? CFQUERY Name=GetDays Datasource=#DSN# Select WS_WeekDay from

Re: How to order by days of the week, but not alphabetically in this query

If it was that easy to change both your table's data and your application(s), then it would probably have been more straightforward to just store the day number (1 to 7) in the table and use the DayOfWeekAsString() function to display the weekday name. It's only really a problem when you've got

RE: How to order by days of the week, but not alphabetically in this query

Well, now that's what I was looking for! I hadn't used the DayOfWeekAsString() function before, so it hadn't come to mind.I knew there just had to be some function that did what I needed. I'll just change the code to store 1-7 to represent the Week Days as display using the DayOfWeekAsString()