September 3, 2002 at 10:14 am
I have the following case statement in my select
CASE WHEN DATENAME(weekday, (CONVERT(varchar(10),mydate,103))) = 'Friday'
THEN 1
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Saturday'
THEN 2
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Sunday'
THEN 3
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Monday'
THEN 4
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Tuesday'
THEN 5
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Wednesday'
THEN 6
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Thursday'
THEN 7
ELSE 0
END as DayNo
as I need to output the days as a number the problem is I was using
GROUP BY CONVERT(varchar(10),mydate,103)))
and I get an error saying I cannot use mydate in the select as it is not in the group by.
If I place just
CONVERT(varchar(10),mydate,103))) in the select without the surrounding datename function it works but then I don't get my numeric days with friday as 1. Any neat solutions?
Nigel Moore
======================
September 3, 2002 at 11:40 am
Hi!
In your case, you should be able to repeat the entire CASE statement (minus the alias) as a GROUP BY :
GROUP BY
CASE WHEN DATENAME(weekday, (CONVERT(varchar(10),mydate,103))) = 'Friday'
THEN 1
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Saturday'
THEN 2
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Sunday'
THEN 3
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Monday'
THEN 4
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Tuesday'
THEN 5
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Wednesday'
THEN 6
WHEN DATENAME(weekday, CONVERT(varchar(10),mydate,103)) = 'Thursday'
THEN 7
ELSE 0
END
Hope this works for you -- good luck!!
Best regards,
SteveR
Stephen Rosenbach
September 4, 2002 at 7:59 am
Going for the radically different approach here :
There is an option, DATEFIRST, that indicates the first day of the week. You can set it to friday (5, that is). Check BOL for details. I'm not sure if it is a database setting, or a connection specific setting...
Then, you can use the DATEPART function, to get the number for the weekday as in
DATEPART(dw, MyDate)
Don't know how the 'GROUP BY' reacts when you use these functions.
September 4, 2002 at 9:28 am
When I have queries with complicated grouping needs, I frequently just select the results into a table variable along with another column that makes it easier for me to sort/group. I've found that in many cases, explicitely controlling the "staging" table gives me overall faster results than depending strictly on what SS will come up with.
In your case, I'd select that first statement into a table variable, then have another SELECT pull it out sorted or grouped.
- Troy King
- Troy King
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply