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)

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

Start the conversation

0 comments

Register

I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.