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.