Sorting the days of the week with Monday first

I have to display a name, hiredate and day of the week on which an employee started. I have to label the column DAY, and order the results by the day of the week STARTING WITH MONDAY. When I run my query it returns the data as in the table, i.e. with Tuesday first. How do I get the day of the week in the correct order?

I was with you all the way until you said the data in the table has Tuesday first. This suggests that there's some kind of weird day-of-week setting in your database that has made Tuesday the first day of the week, because alphabetically, it would be Friday (Fri, Mon, Sat, Sun, Thu, Tue, Wed), and numerically, it's usually 0 or 1, and it's usually Sunday or Monday.

I am familiar with (if that's not too strong a word, because I've never had to change it) the day-of-week setting only in Microsoft SQL/Server -- if you are running some other database system, look for an equivalent setting. In Microsoft SQL/Server, the week starts on day 7 which is Sunday; this is the US default, but it can be set to any value in the range 1 to 7. If you do not know how to issue the SET DATEFIRST command, see your database administrator. If you set DATEFIRST to 1, then the week will start on Monday and the day-of-week number for a Thursday, say, will be 4. Then when you use the DATEPART(dw,yourDate) expression in the ORDER BY clause, Mondays will sort first because they have a dw or day-of-week value of 1. You would use DATENAME(dw,yourDate) to show the name of the day of the week.

  select LEFT(DATENAME(dw,HireDate),3) as HireDay 
       , Name, HireDate
    from Employee
   where ...
order by DATEPART(dw,HireDate)

This yields results like --

 HireDay  Name        HireDate
   Mon   John Dough   2002-02-25
   Mon   Mark Snow    2002-03-04
   Mon   David Know   2002-03-11
   Tue   Mary Jones   2002-02-26
   Tue   Tina Tones   2002-03-05

If you're not on SQL/Server (or even if you are), you may want to try this alternate strategy. It merely requires that you have a day-name function. In MySQL, for instance, you have DAYNAME(yourDate), and you can take LEFT(DAYNAME(yourDate),3) to get the values 'Mon', 'Tue', and so on. In Oracle you would use TO_CHAR(DY,yourDate) to get the same 3-character values. Then you simply create a handy-dandy WeekDay sorting table that looks like this --

DayName Sort
  Mon    1
  Tue    2
  Wed    3
  Thu    4
  Fri    5
  Sat    6
  Sun    7

And join it to your data --

  select LEFT(DAYNAME(HireDate),3) as HireDay 
       , Name, HireDate
    from Employee, WeekDay
   where LEFT(DAYNAME(HireDate),3) = Weekday.DayName
     and ...
order by Weekday.Sort

The "brute force" method.

For More Information

• What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.