Order by day column using SQL
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 az 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.
TransactSQL’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 TransactSQL’s CASE expression see http://msdn.microsoft.com/enus/library/ms181765.aspx.
Categorized as Uncategorized
2 Comments
Trackbacks & Pingbacks

Dave the Ninja
Yo Duffy!
DRY it up! Extract the Case to a scalar valuer function and order on that!
CREATE FUNCTION Convert_DayNameToDayNumber
(
@DayOfTheWeek nvarchar(10)
)
RETURNS int
AS
BEGIN
RETURN CASE @DayOfTheWeek
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
END
GO——————————
SELECT
*
FROM
classes
ORDER BY
Convert_DayNameToDayNumber(Day) ASC
comment_type == "trackback"  $comment>comment_type == "pingback"  ereg(" 
Colin Wiseman
Awesome thing William. Dave, that takes it to the next level. But then, you should fire the person that wrote a system that saved the day of the week as a string!
Yo Duffy!
DRY it up! Extract the Case to a scalar valuer function and order on that!
CREATE FUNCTION Convert_DayNameToDayNumber
(
@DayOfTheWeek nvarchar(10)
)
RETURNS int
AS
BEGIN
RETURN CASE @DayOfTheWeek
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
END
GO
——————————
SELECT
*
FROM
classes
ORDER BY
Convert_DayNameToDayNumber(Day) ASC
Awesome thing William. Dave, that takes it to the next level. But then, you should fire the person that wrote a system that saved the day of the week as a string!