cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
205
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

Everyone's tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions
Rising star

Re: Converting Day number to Day

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
2 REPLIES 2
Rising star

Re: Converting Day number to Day

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
Enthusiast

Re: Converting Day number to Day

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

CreatePlease to create content
Content for Community-Ad
July's Community Spotlight Awards