Converting Day number to Day

[sarbarnes]

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

[Jameson Gagnepain · Accepted solution]

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

[sarbarnes]

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