How do I calculate the week number given a date?
Solution 1:
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.
Solution 2:
Pseudocode:
int julian = getDayOfYear(myDate) // Jan 1 = 1, Jan 2 = 2, etc...
int dow = getDayOfWeek(myDate) // Sun = 0, Mon = 1, etc...
int dowJan1 = getDayOfWeek("1/1/" + thisYear) // find out first of year's day
// int badWeekNum = (julian / 7) + 1 // Get our week# (wrong! Don't use this)
int weekNum = ((julian + 6) / 7) // probably better. CHECK THIS LINE. (See comments.)
if (dow < dowJan1) // adjust for being after Saturday of week #1
++weekNum;
return (weekNum)
To clarify, this algorithm assumes you number your weeks like this:
S M T W R F S
1 2 3 <-- week #1
4 5 6 7 8 9 10 <-- week #2
[etc.]
getDayOfWeek() and getDayOfYear() are standard date-object operations in most languages. If yours doesn't have them, you can count-forward from some known date (Jan 1, 1970 is a common one), after looking up to see what day of the week it was.
If you're going to implement your own date counting routines, remember that years that are divisible by 100 are NOT leap years, unless they are also divisible by 400. So 1900 was not a leap year, but 2000 was. If you're going to work far back in time, you have to mess with Gregorian vs Julian calendars, etc., see Wikipedia for loads of info on that.
This link talks about date/time functions in Windows/C++ in greater detail.
Solution 3:
I strongly recommend using the C Standard Library's time functions to calculate the week number. Specifically, the strftime function has specifiers to print the week number (among many other values) given a date in broken-down (struct tm) format. Here's a small sample program that illustrates this:
#include <stdio.h>
#include <string.h>
#include <time.h>
int
main(void)
{
struct tm tm;
char timebuf[64];
// Zero out struct tm
memset(&tm, 0, sizeof tm);
// November 4, 2008 11:00 pm
tm.tm_sec = 0;
tm.tm_min = 0;
tm.tm_hour = 23;
tm.tm_mday = 4;
tm.tm_mon = 10;
tm.tm_year = 108;
tm.tm_isdst = -1;
// Call mktime to recompute tm.tm_wday and tm.tm_yday
mktime(&tm);
if (strftime(timebuf, sizeof timebuf, "%W", &tm) != 0) {
printf("Week number is: %s\n", timebuf);
}
return 0;
}
The output from this program (compiled with GCC on Linux and Microsoft Visual Studio 2005 SP1 on Windows) is:
Week number is: 44
You can learn more about strftime here.