Grouping dates by week

I have a field called FieldA which has a date value.  I would like to group the dates by week. Week1 - Week52.  

What syntax can I use to assign a week number to each respective date.
We will use Sunday as the first day of the week and Saturday as the last day of the week.
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
theartfuldazzlerCommented:
Hi Morinia

I know you have asked SAS questions before, so here is the SAS solution:

DATA Input_DS;
  Date = '01Jan2012'd;
  OUTPUT;
  DATE = '31Dec2011'd;
  OUTPUT;
  DATE = '29JUL2009'd;
  OUTPUT;
  FORMAT Date Date9.;
RUN;

DATA Output_DS;
  Set Input_DS;
    Start_of_year = MDY(1,1, YEAR(Date));
	Week_no = INTCK("WEEK",Start_of_year,Date)+1;  
	/* One might need to make adjustments to the +1 depending on how one counts the first week */
	DROP Start_of_year;
RUN;

Open in new window

0
 
Wasim Akram ShaikCommented:
you can use this for getting the week number

select to_char(sysdate,'IW') from dual;

and use the same thing in group by also..
0
 
DcpKingCommented:
Assuming SQL Server (for Oracle see previous answer):

select datepart(week, @dtDateValue) will return an integer corresponding to the ISO week number (week 1 is the week containing the first Thursday in the year; weeks start on Sunday).

Thus try grouping on datepart(week, FieldA):

select datepart(week, FieldA) as 'WeekNr', OtherField1, OtherField2, .....
from TableName
group by datepart(week, FieldA)

should give you the results with the week number at the front.

(Apologies for any typos - I don't have a copy of SQL Server in front of me to check things with)

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.