order day sql select

Question

Good Day! I have a table filled with date time datatype it's stored as nvarchar. I need to select from the table and get result sorted by day the day is inserted in this shape.

>Thursday 11:30-12:30
>Sunday 08:00-09:00
>Sunday 08:00-09:00
>Sunday 08:00-09:00
>Sunday 08:00-09:00
>Monday 08:00-09:00
>Monday 09:00-10:00
>Monday 08:00-09:00

i have tried using this

SELECT TOP 1000 [Class_Time_ID] ,[Class_ID] ,[Class_Date]
FROM [School].[dbo].[Class_Time_Table] 
ORDER BY CASE Class_Date WHEN 'Monday' THEN 2 
                         WHEN 'Tuesday' THEN 3 
                         WHEN 'Wednesday' THEN 4 
                         WHEN 'Thursday' THEN 5 
                         WHEN 'Sunday' THEN 1
         end 

Answer 1

Using CROSS APPLY may be helpful as it then permits use of an alias for the case expression in both the select and where clauses. e.g.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Class_Time_Table
    ([Class_Date] varchar(20), [Class_Time_ID] int ,[Class_ID] int)
;

INSERT INTO Class_Time_Table
    ([Class_Date])
VALUES
    ('Thursday 11:30-12:30'),
    ('Sunday 08:00-09:00'),
    ('Sunday 08:00-09:00'),
    ('Sunday 08:00-09:00'),
    ('Sunday 08:00-09:00'),
    ('Monday 08:00-09:00'),
    ('Monday 09:00-10:00'),
    ('Monday 08:00-09:00')
;

Query 1:

SELECT
       ctt.*
      , ca.day_of_week,ca.start_time,ca.end_time
FROM Class_Time_Table ctt
CROSS APPLY (
      SELECT
            CASE
                  WHEN class_date LIKE 'Monday%' THEN 2
                  WHEN class_date LIKE 'Tuesday%' THEN 3
                  WHEN class_date LIKE 'Wednesday%' THEN 4
                  WHEN class_date LIKE 'Thursday%' THEN 5
                  WHEN class_date LIKE 'Sunday%' THEN 1
            END                                    AS day_of_week
          , SUBSTRING(RIGHT(class_date, 11), 1, 5) AS start_time
          , SUBSTRING(RIGHT(class_date, 5) , 1, 5)  AS end_time
  ) ca 
order by day_of_week, start_time, end_time

Results:

|           Class_Date | Class_Time_ID | Class_ID | day_of_week | start_time | end_time |
|----------------------|---------------|----------|-------------|------------|----------|
|   Sunday 08:00-09:00 |        (null) |   (null) |           1 |      08:00 |    09:00 |
|   Sunday 08:00-09:00 |        (null) |   (null) |           1 |      08:00 |    09:00 |
|   Sunday 08:00-09:00 |        (null) |   (null) |           1 |      08:00 |    09:00 |
|   Sunday 08:00-09:00 |        (null) |   (null) |           1 |      08:00 |    09:00 |
|   Monday 08:00-09:00 |        (null) |   (null) |           2 |      08:00 |    09:00 |
|   Monday 08:00-09:00 |        (null) |   (null) |           2 |      08:00 |    09:00 |
|   Monday 09:00-10:00 |        (null) |   (null) |           2 |      09:00 |    10:00 |
| Thursday 11:30-12:30 |        (null) |   (null) |           5 |      11:30 |    12:30 |