nasty Day of Week conversion | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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

]]>