If you’ve ever had to sort a SQL query’s resultset using an “order by” clause on a “day of the week” column then you will know it can be very easy, or very tricky, depending on the format that the day is stored in the database.

If you stored the day of the week in numerical format (1 = Sunday, 7 = Saturday) then sorting can be accomplished via a simple order by clause.

1
2
3
4
5
6

SELECT
     *
FROM
     classes
ORDER BY
     day ASC

However, if you have saved the day of the week in textual format then things become a little trickier.

The problem is “order by” on a textual value sorts alphanumerically from a-z and has no comprehension about the order of weekdays. In this situation Thursday comes before Wednesday, Saturday before Tuesday, etc. To get around this we need to let the SQL query know what order to handle the weekdays in.

Transact-SQL’s CASE expression is perfect for this task. All we have to do is assess the day string in the “order by” clause and return a numerical value depending on the result of the expression.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT
     *
FROM
     classes
ORDER BY 
     CASE
          WHEN Day = 'Sunday' THEN 1
          WHEN Day = 'Monday' THEN 2
          WHEN Day = 'Tuesday' THEN 3
          WHEN Day = 'Wednesday' THEN 4
          WHEN Day = 'Thursday' THEN 5
          WHEN Day = 'Friday' THEN 6
          WHEN Day = 'Saturday' THEN 7
     END ASC

The resultset will now be sorted based on the textual “day of the week” field, exactly as it would be if the field held a numeric value.

For more details on Transact-SQL’s CASE expression see http://msdn.microsoft.com/en-us/library/ms181765.aspx.