Group by DatePart

[Spoogledrummer]

Hi, I've run across a problem with on one of the applications I work on. A report uses the group by datepart (wk,Date) but as this is 2007 it messes up as it sees there being 53 weeks so breaks a week up in to 2 parts despite it being the same week. e.g

select datepart (wk,'12/31/2007')

would result in 53 where as

select datepart (wk,'1/1/2008')

would result in 1 despite them being Monday and Tuesday respectively. Now I've read that you can run a check to force the 53 to be a 1 but I'm not sure how that can work when I'm only using datepart in the group by clause. Any one have any ideas on this?

[Spoogledrummer]

Well I have the sql part sorted I think

group by 
CASE
WHEN DATEPART(wk,date) = 1
OR DATEPART(wk,date) = DATEPART(wk, CAST(CAST(YEAR(Date) AS VARCHAR(4)) + '-12-31' AS DATETIME))
THEN 0 ELSE DATEPART(wk,date) END

now to figure out why it's not working in the report