nasty Day of Week conversion

I have a table that has the day of week as a text field and is associated with a semester, not a particular date. I want to order by this field in the correct day of week order (Monday, Tuesday, Wednesday, Thursday, Friday). I finally got the following code to work but was looking for a cleaner solution.

iif(Day_of_Week='Monday',1,iif(Day_of_Week='Tuesday',2,iif(Day_of_Week='Wednesday',3,iif(Day_of_Week='Thursday',4,iif(Day_of_Week='Friday',5,0)))))

and then...

Order by iif(Day_of_Week='Monday',1,iif(Day_of_Week='Tuesday',2,iif(Day_of_Week='Wednesday',3,iif(Day_of_Week='Thursday',4,iif(Day_of_Week='Friday',5,0)))))

Thanks Tom <><<


Tom Laurie
CIS Professor
NH Technical Institute

You could try something like this for a bit cleaner code, I think it would probably be a little faster as well as you're not repeating yourself in the order by clause :

select my_day_of_week =
case my_day_of_week_field_name
when 'Monday' then 1
when 'Tuesday' then 2
when 'Wednesday' then 3
when 'Thursday' then 4
when 'Friday' then 5
end
order by my_day_of_week

I haven't seen any intrinsic functions that convert the day in a text format to a numeric value except for an enumeration in sql dmo, anyone else know of a function in sql server to do this?

Cheers
Shaun

World Domination Through Superior Software

Built in function in SQL
DATENAME(dw, [date field]) as sShowDayText