Returns the ISO week number (ISO8601:2000 Standard). All weeks begin on a Monday. Week 1 starts on Monday of the first week of the calendar year with a Thursday.

 

The International Organization for Standardization issued Standard 8601 -- Representation Of Dates And Times, in 1988. This provides some standardization for "week numbers." Since compliance with these standards is entirely voluntary, your business may or may not use the ISO definitions.

 

Under the ISO standard, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains January 4th.

 

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was January 6th, so week 1 begins the preceding Monday, or January 3rd. Therefore, the first two days of 2000, January 1st and January 2nd, fall into week 52 of 1999.

 

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If January 1st falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

 

 

Example:

SELECT WEEK({d '2006-11-19'}) returns 46

 

The results of the SQL expression are shown in the first column.