Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I calculate the week number given a date?

If I have a date, how do I calculate the week number for that date within that year?

For example, in 2008, January 1st to January 6th are in week 1 and January 7th to the 13th are in week 2, so if my date was January 10th 2008, my week number would be 2.

An algorithm would be great to get me started and sample code would also help - I'm developing in C++ on Windows.

Related:

Getting week number off a date in MS SQL Server 2005?

like image 737
Big GH Avatar asked Nov 08 '08 16:11

Big GH


People also ask

How do I calculate weeks from a date in Excel?

To find out how many weeks there are between two dates, you can use the DATEDIF function with "D" unit to return the difference in days, and then divide the result by 7.

How do I automate the week number in Excel?

1. Select a blank cell you will return the week number, enter this formula: =WEEKNUM(B1,1), and press the Enter key.


1 Answers

Be aware that while your definition of nth week of the year is tenable, it is also not 'the' standard one.

ISO 8601 defines a standard for the representation of dates, times and time zones. It defines weeks that start on a Monday. It also says Week 1 of a year is the one which contains at least 4 days from the given year. Consequently, the 29th, 30th and 31st of December 20xx could be in week 1 of 20xy (where xy = xx + 1), and the 1st, 2nd and 3rd of January 20xy could all be in the last week of 20xx. Further, there can be a week 53.

[Added: note that the C standard and the `strftime() function provides for weeks that start on Sunday as well as weeks that start on Monday. It is not clear that the C standard provides for the year number of week 0 for Sunday-based weeks. See also the answer from Emerick Rogul.]

Then comes the interesting testing phase -- when do you get week 53? One answer is on Friday 1st January 2010, which is in 2009-W53 (as, indeed, is Sunday 3rd January 2010). Similarly, Saturday 1st January 2005 is in 2004-W53, but Sunday 1st January 2006 is in 2005-W52.

That is an extract from a comment in the following code, which is actually in Informix SPL (Stored Procedure Language), but is readable - though probably not writable - without much further explanation. The '||' operator is the SQL string concatenation operation, and Sunday is day 0, Monday is day 1, ... Saturday is day 6 of the week. There are extensive notes in the comments, including relevant text from the standard. One line comments start '--'; possibly multiline comments start with '{' and end at the next '}'.

-- @(#)$Id: iso8601_weekday.spl,v 1.1 2001/04/03 19:34:43 jleffler Exp $ -- -- Calculate ISO 8601 Week Number for given date -- Defines procedure: iso8601_weekday(). -- Uses procedure: iso8601_weeknum().  { According to a summary of the ISO 8601:1988 standard "Data Elements and Interchange Formats -- Information Interchange -- Representation of dates and times":      The week notation can also be extended by a number indicating the     day of the week.  For example the day 1996-12-31 which is the     Tuesday (day 2) of the first week of 1997 can also be written as          1997-W01-2 or 1997W012      for applications like industrial planning where many things like     shift rotations are organized per week and knowing the week number     and the day of the week is more handy than knowing the day of the     month.  This procedure uses iso8601_weeknum() to format the YYYY-Www part of the date, and appends '-d' to the result, allowing for Informix's coding of Sunday as day 0 rather than day 7 as required by ISO 8601. }  CREATE PROCEDURE iso8601_weekday(dateval DATE DEFAULT TODAY) RETURNING CHAR(10);     DEFINE rv CHAR(10);     DEFINE dw CHAR(4);     LET dw = WEEKDAY(dateval);     IF dw = 0 THEN             LET dw = 7;     END IF;     RETURN iso8601_weeknum(dateval) || '-' || dw; END PROCEDURE; -- @(#)$Id: iso8601_weeknum.spl,v 1.1 2001/02/27 20:36:25 jleffler Exp $ -- -- Calculate ISO 8601 Week Number for given date -- Defines procedures: day_one_week_one() and iso8601_weeknum().  { According to a summary of the ISO 8601:1988 standard "Data Elements and Interchange Formats -- Information Interchange -- Representation of dates and times":      In commercial and industrial applications (delivery times,     production plans, etc.), especially in Europe, it is often required     to refer to a week of a year.  Week 01 of a year is per definition     the first week which has the Thursday in this year, which is     equivalent to the week which contains the fourth day of January.  In     other words, the first week of a new year is the week which has the     majority of its days in the new year.  Week 01 might also contain     days from the previous year and the week before week 01 of a year is     the last week (52 or 53) of the previous year even if it contains     days from the new year.  A week starts with Monday (day 1) and ends     with Sunday (day 7).  For example, the first week of the year 1997     lasts from 1996-12-30 to 1997-01-05 and can be written in standard     notation as          1997-W01 or 1997W01      The week notation can also be extended by a number indicating the     day of the week.  For example the day 1996-12-31 which is the     Tuesday (day 2) of the first week of 1997 can also be written as          1997-W01-2 or 1997W012      for applications like industrial planning where many things like     shift rotations are organized per week and knowing the week number     and the day of the week is more handy than knowing the day of the     month.  Referring to the standard itself, section 3.17 defines a calendar week:      week, calendar: A seven day period within a calendar year, starting     on a Monday and identified by its ordinal number within the year;     the first calendar week of the year is the one that includes the     first Thursday of that year.  In the Gregorian calendar, this is     equivalent to the week which includes 4 January.  Section 5.2.3 "Date identified by Calendar week and day numbers" states:      Calendar week is represented by two numeric digits.  The first     calendar week of a year shall be identified as 01 [...]      Day of the week is represented by one decimal digit.  Monday     shall be identified as day 1 of any calendar week [...]  Section 5.2.3.1 "Complete representation" states:      When the application clearly identifies the need for a complete     representation of a date identified by calendar week and day     numbers, it shall be one of the alphanumeric representations as     follows, where CCYY represents a calendar year, W is the week     designator, ww represents the ordinal number of a calendar week     within the year, and D represents the ordinal number within the     calendar week.      Basic format: CCYYWwwD         Example: 1985W155     Extended format: CCYY-Www-D         Example: 1985-W15-5  Both the summary and the formal definition are intuitively clear, but it is not obvious how to translate it into an algorithm.  However, we can deal with the problem by exhaustively enumerating the seven options for the day of the week on which 1st January falls (with actual year values for concreteness):      1st January 2001 is Monday    => Week 1 starts on 2001-01-01     1st January 2002 is Tuesday   => Week 1 starts on 2001-12-31     1st January 2003 is Wednesday => Week 1 starts on 2002-12-30     1st January 2004 is Thursday  => Week 1 starts on 2003-12-29     1st January 2010 is Friday    => Week 1 starts on 2010-01-04     1st January 2005 is Saturday  => Week 1 starts on 2005-01-03     1st January 2006 is Sunday    => Week 1 starts on 2006-01-02  (Cross-check: 1st January 1997 was a Wednesday; the summary notes state that week 1 of 1997 started on 1996-12-30, which is consistent with the table derived for dates in the first decade of the third millennium above).  When working with the Informix DATE types, bear in mind that Informix uses WEEKDAY values 0 = Sunday, 1 = Monday, 6 = Saturday.  When the weekday of the first of January has the value in the LH column, you need to add the value in the RH column to the 1st of January to obtain the date of the first day of the first week of the year.      Weekday         Offset to     1st January     1st day of week 1      0               +1     1                0     2               -1     3               -2     4               -3     5               +3     6               +2  This can be written as MOD(11-w,7)-3 where w is the (Informix encoding of the) weekday of 1st January and the value 11 is used to ensure that no negative values are presented to the MOD operator.  Hence, the expression for the date corresponding to the 1st day (Monday) of the 1st week of a given year, yyyy, is:      d1w1 = MDY(1, 1, yyyy) + MOD(11 - WEEKDAY(MDY(1,1,yyyy)), 7) - 3  This expression is encapsulated in stored procedure day_one_week_one: }  CREATE PROCEDURE day_one_week_one(yyyy INTEGER) RETURNING DATE;     DEFINE jan1 DATE;     LET jan1 = MDY(1, 1, yyyy);     RETURN jan1 + MOD(11 - WEEKDAY(jan1), 7) - 3; END PROCEDURE;  { Given this date d1w1, we can calculate the week number of any other date in the same year as:      TRUNC((dateval - d1w1) / 7) + 1  The residual issues are ensuring that the wraparounds are correct.  If the given date is earlier than the start of the first week of the year that contains it, then the date belongs to the last week of the previous year.  If the given date is on or after the start of the first week of the next year, then the date belongs to the first week of the next year.  Given these observations, we can write iso8601_weeknum as shown below. (Beware: iso8601_week_number() is too long for servers with the 18-character limit; so is day_one_of_week_one()).  Then comes the interesting testing phase -- when do you get week 53? One answer is on Friday 1st January 2010, which is in 2009-W53 (as, indeed, is Sunday 3rd January 2010).  Similarly, Saturday 1st January 2005 is in 2004-W53, but Sunday 1st January 2006 is in 2005-W52. }  CREATE PROCEDURE iso8601_weeknum(dateval DATE DEFAULT TODAY) RETURNING CHAR(8);     DEFINE rv CHAR(8);     DEFINE yyyy CHAR(4);     DEFINE ww CHAR(2);     DEFINE d1w1 DATE;     DEFINE tv DATE;     DEFINE wn INTEGER;     DEFINE yn INTEGER;     -- Calculate year and week number.     LET yn = YEAR(dateval);     LET d1w1 = day_one_week_one(yn);     IF dateval < d1w1 THEN         -- Date is in early January and is in last week of prior year         LET yn = yn - 1;         LET d1w1 = day_one_week_one(yn);     ELSE         LET tv = day_one_week_one(yn + 1);         IF dateval >= tv THEN             -- Date is in late December and is in the first week of next year             LET yn = yn + 1;             LET d1w1 = tv;         END IF;     END IF;     LET wn = TRUNC((dateval - d1w1) / 7) + 1;     -- Calculation complete: yn is year number and wn is week number.     -- Format result.     LET yyyy = yn;     IF wn < 10 THEN         LET ww = '0' || wn;     ELSE         LET ww = wn;     END IF     LET rv = yyyy || '-W' || ww;     RETURN rv; END PROCEDURE; 

For completeness, the inverse function is also easy to write with the day_one_week_one() function above:

-- @(#)$Id: ywd_date.spl,v 1.1 2012/12/29 05:13:27 jleffler Exp $ -- @(#)Create ywd_date() and ywdstr_date() stored procedures  -- Convert a date in format year, week, day (ISO 8601) to DATE. -- Two variants: -- ywd_date(yyyy SMALLINT, ww SMALLINT, dd SMALLINT) RETURNING DATE; -- ywdstr_date(ywd CHAR(10)) RETURNING DATE;  -- NB: If week 53 is supplied, there is no check that the year had week --     53 (GIGO). -- NB: If year yyyy is a leap year and yyyy-01-01 falls on Wed (3) or --     Thu (4), there are 53 weeks in the year. -- NB: If year yyyy is not a leap year and yyyy-01-01 falls on Thu (4), --     there are 53 weeks in the year.  CREATE PROCEDURE ywd_date(yyyy SMALLINT, ww SMALLINT, dd SMALLINT) RETURNING DATE AS date;     DEFINE d DATE;     -- Check ranges     IF yyyy < 1 OR yyyy > 9999 OR ww < 1 OR ww > 53 OR dd < 1 OR dd > 7 THEN         RETURN NULL;     END IF;     LET d = day_one_week_one(yyyy);     LET d = d + (ww - 1) * 7 + (dd - 1);     RETURN d; END PROCEDURE;  -- Input: 2012-W52-5 CREATE PROCEDURE ywdstr_date(ywd CHAR(10)) RETURNING DATE AS date;     DEFINE yyyy SMALLINT;     DEFINE ww   SMALLINT;     DEFINE dd   SMALLINT;     LET yyyy = SUBSTR(ywd,  1, 4);     LET ww   = SUBSTR(ywd,  7, 2);     LET dd   = SUBSTR(ywd, 10, 1);     RETURN ywd_date(yyyy, ww, dd); END PROCEDURE;  CREATE TEMP TABLE test_dates(d DATE); INSERT INTO test_dates VALUES('2011-12-28'); INSERT INTO test_dates VALUES('2011-12-29'); INSERT INTO test_dates VALUES('2011-12-30'); INSERT INTO test_dates VALUES('2011-12-31'); INSERT INTO test_dates VALUES('2012-01-01'); INSERT INTO test_dates VALUES('2012-01-02'); INSERT INTO test_dates VALUES('2012-01-03'); INSERT INTO test_dates VALUES('2012-01-04'); INSERT INTO test_dates VALUES('2012-01-05'); INSERT INTO test_dates VALUES('2012-01-06'); INSERT INTO test_dates VALUES('2012-01-07');  SELECT d, iso8601_weeknum(d), iso8601_weekday(d), ywdstr_date(iso8601_weekday(d))   FROM test_dates  ORDER BY d; 

As noted in the comments, the code will accept a week 53 date even if the year should only accept 52 weeks.

like image 75
Jonathan Leffler Avatar answered Oct 07 '22 19:10

Jonathan Leffler