cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
916
Views
0
Helpful
2
Replies

Converting Day number to Day

sarbarnes
Level 4
Level 4


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

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

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

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

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: