CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jainendra
Jainendra

Reputation: 25143

Sort table data on weekday

I have a SQL Server table like this:

table(Id, DestId, WeekDay) WeekDay is nvarchar holding values like Monday, Tuesday, Wednesday, etc. Here Id and Weekday are composite primary keys. I want the result of select query to be sorted on WeekDay order, i.e. Monday should come before Tuesday and Wednesday and so on.

Write now Select * from table where Id=1001 gives records sorted on WeekDays Alphabetic order

Upvotes: 1

Views: 177

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44326

Having weekday as plain text in a table is not really good practice. You should use a smallint to store the Weekday value instead. Having that said. Here is a solution.

By making a script to test different combinations, I came up with this combination of Weekday and 2 other characters.

SELECT WeekDay 
FROM
  (VALUES('Monday'),('Tuesday'),('Wednesday'),('Thursday'),
         ('Friday'),('Saturday'),('Sunday')) x(WeekDay)
ORDER BY HASHBYTES('SHA1',WeekDay + 'g2')

Note it is case sensitive on the days.

Upvotes: 0

Jesuraja
Jesuraja

Reputation: 3844

You can try this:

SELECT * FROM Table ORDER BY CHARINDEX(LEFT(WeekDay,3),'MonTueWedThuFriSatSun')

CHARINDEX(LEFT(WeekDay,3),'MonTueWedThuFriSatSun') will return the index position. If you want to sort from Sunday, the you can change the string to 'SunMonTueWedThuFriSat'

Upvotes: 2

Vigi Tri
Vigi Tri

Reputation: 156

This should work:

Select
 CASE WeekDay
   WHEN 'Monday' THEN 1
   WHEN 'Tuesday' THEN 2
   WHEN 'Wednesday' THEN 3
   WHEN 'Thursday' THEN 4
   WHEN 'Friday' THEN 5
   WHEN 'Saturday' THEN 6
   WHEN 'Sunday' THEN 7 End
 AS SortWeekday
FROM [TABLE]
WHERE Id = 1001
OrderBy SortWeekday

Upvotes: 1

Nicolas Henrard
Nicolas Henrard

Reputation: 843

Solution here: http://dbaspot.com/sqlserver-faq/393109-order-day-week.html

You have to make a case structure in your SQL statement like this one:

select *
from table
ORDER BY case WeekDay when 'Monday' then 1
    when 'Tuesday' then 2
    when 'Wednesday' then 3
    when 'Thursday' then 4
    when 'Friday' then 5
    when 'Saturday' then 6
    when 'Sunday' then 7
    else 8
END
;

Upvotes: 2

Related Questions