cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements
299
Views
0
Helpful
2
Replies
Highlighted
Enthusiast

Converting Day number to Day


I am trying to run a query that converts the Day number as in 1 = Monday, so I can run a graphical report with the Days in order...

However no matter what I try I can't get it to work! I have tried adding CASE When 1 = 'Monday', I have tried to Getdate, however I can't get it to work... Any help is welcome.

SELECT DateTime,

DATEPART (year, DateTime) AS Year,

DATENAME (year, DateTime) + 'Q' + DATENAME (quarter, DateTime) AS Quarter_Date,

DATEADD(month, DATEDIFF(month, 0, DateTime),0) AS Month_Date,

DATEADD(day, DATEDIFF(day, 0, DateTime),0) AS Day_Date,

DATEPART (quarter, DateTime) AS Quarter,

DATENAME (month, DateTime) AS Month,

DATENAME (weekday, DateTime ) AS Weekday,

DATEPART (weekday, DateTime) AS Weekday_number,

DATEPART (hour, DateTime) AS Hour,

NewCallTo5,

LateCallsTo5,

TimeoutCallsTo5

FROM

Routing_Client_Five_Minute

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Rising star

Sounds like you built the CASE statement wrong... The number 1 is never going to equal the string 'Monday'. Try this:

CASE DATEPART (weekday, DateTime)
      WHEN 2 THEN '1 Monday'
      WHEN 3 THEN '2 Tuesday'
      WHEN 4 THEN '3 Wednesday'
      WHEN 5 THEN '4 Thursday'
      WHEN 6 THEN '5 Friday'
      WHEN 7 THEN '6 Saturday'
      WHEN 1 THEN '7 Sunday'
      ELSE 'Not a day'
END AS WeekdayName,

This should give you a set of strings that will sort properly on the chart. You can of course change the Sunday string to '1 Sunday' and shift the others around if you'd like to start the week with Sunday.

-Jameson

-Jameson

View solution in original post

2 REPLIES 2
Highlighted
Rising star

Sounds like you built the CASE statement wrong... The number 1 is never going to equal the string 'Monday'. Try this:

CASE DATEPART (weekday, DateTime)
      WHEN 2 THEN '1 Monday'
      WHEN 3 THEN '2 Tuesday'
      WHEN 4 THEN '3 Wednesday'
      WHEN 5 THEN '4 Thursday'
      WHEN 6 THEN '5 Friday'
      WHEN 7 THEN '6 Saturday'
      WHEN 1 THEN '7 Sunday'
      ELSE 'Not a day'
END AS WeekdayName,

This should give you a set of strings that will sort properly on the chart. You can of course change the Sunday string to '1 Sunday' and shift the others around if you'd like to start the week with Sunday.

-Jameson

-Jameson

View solution in original post

Highlighted

Thanks jameson that has corrected it for me.. that was the only permitation I didn't try :-)

Content for Community-Ad