SQL Server Performance

nasty Day of Week conversion

Discussion in 'T-SQL Performance Tuning for Developers' started by tlaurie, Dec 12, 2002.

  1. tlaurie New Member

    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
  2. trifunk New Member

    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
  3. twagner New Member

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

Share This Page