By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,688 Members | 1,877 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,688 IT Pros & Developers. It's quick & easy.

Group by DatePart

P: 21
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
Expand|Select|Wrap|Line Numbers
  1. select datepart (wk,'12/31/2007')
would result in 53 where as
Expand|Select|Wrap|Line Numbers
  1. 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?
Feb 26 '08 #1
Share this Question
Share on Google+
1 Reply

P: 21
Well I have the sql part sorted I think
Expand|Select|Wrap|Line Numbers
  1. group by 
  2. CASE
  3. WHEN DATEPART(wk,date) = 1
  4. OR DATEPART(wk,date) = DATEPART(wk, CAST(CAST(YEAR(Date) AS VARCHAR(4)) + '-12-31' AS DATETIME))
  5. THEN 0 ELSE DATEPART(wk,date) END
now to figure out why it's not working in the report
Feb 26 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.