Can I select a specific ORDER BY in SQL Server 2008?

Question

I have a table that contains days and times, the column of the day can contain any of the seven days entered into it, and they are configured for the varchar data type. Since this table contains the reservation time for the client, I want to select all the days from the table where the identifier matches, and I want to sort from Mondays to Sunday. I was hoping that I could add something to this query to manually select the order of returning results as follows:

 select * from requirements where Family_ID = 1 ORDER BY Day, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday 

This, of course, does not work, but I just wanted to show what I'm trying to achieve. The client does not necessarily require help every day, I just want to show the days when they are booked.

Sorting by DESC and ASC does not help with the days of the week, I would appreciate any advice on how to achieve this.

Thanks.

Answer 1

Hmm .. is it unpleasant, days are stored literally as "Monday", "Tuesday", etc.?

In any case, just do the following:

 SELECT * FROM Requirements ORDER BY CASE Day 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 

Answer 2

IMHO you have no reason to store Monday, Tuesday, etc ... If you save the date or date and time value, you can always extract the week name from this data during the query, when you need it - either using DATENAME in your query or Using the functionality of your presentation layer. The cost of doing this does not justify keeping it separate IMHO. And you should still sort correctly using your own date / datetime data.