Q
Problem solve Get help with specific problems with your technologies, process and projects.

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 editor@searchDatabase.com 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.

This was last published in March 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close