Skip to main content

WEEK (SQL)

A date function that returns the week of the year as an integer for a date expression.

Synopsis

{fn WEEK(date-expression)}

Description

WEEK takes a date-expression, and returns the number of weeks from the beginning of the year for that date.

By default, weeks are calculated using the $HOROLOG date (positive or negative integer number of days from Dec. 31, 1840). Therefore, weeks are counted from year to year, such that Week 1 is the days that complete the seven-day period begun by the last week of the previous year. A week always begins with a Sunday; therefore, the first Sunday of the calendar year marks the changing from Week 1 to Week 2. If the first Sunday of the year is January 1, then that Sunday is in Week 1; if the first Sunday of the year is later than January 1, then that Sunday is the first day of Week 2. For this reason, Week 1 is commonly less than seven days in length. You can determine the day of the week by using the DAYOFWEEK function. The total number of weeks in a year is commonly 53, and can be 54 in leap years.

InterSystems IRIS also supports the ISO 8601 standard for determining the week of the year. This standard is principally used in European countries. When InterSystems IRIS is configured for ISO 8601, WEEK begins counting a week with Monday, and assigns the week to the year that contains that week’s Thursday. For example, Week 1 of 2004 ran from Monday 29 December 2003 to Sunday 4 January 2004, because this week’s Thursday was 1 January 2004, which was the first Thursday of 2004. Week 1 of 2005 ran from Monday 3 January 2005 to Sunday 9 January 2005, because its Thursday was 6 January 2005, which was the first Thursday of 2005. The total number of weeks in a year is commonly 52, but can occasionally be 53. To activate ISO 8601 counting, SET ^%SYS("sql","sys","week ISO8601")=1.

The date-expression can be an InterSystems IRIS date integer, a $HOROLOG or $ZTIMESTAMP value, an ODBC format date string, or a timestamp.

A date-expression timestamp can be either data type %Library.PosixTimeOpens in a new tab (an encoded 64-bit signed integer), or data type %Library.TimeStampOpens in a new tab (yyyy-mm-dd hh:mm:ss.fff).

The time portion of the timestamp is not evaluated and can be omitted.

The same week information can be returned by using the DATEPART or DATENAME function.

This function can also be invoked from ObjectScript using the WEEK()Opens in a new tab method call:

$SYSTEM.SQL.Functions.WEEK(date-expression)

Date Validation

WEEK performs the following checks on input values. If a value fails a check, the null string is returned.

  • A date string must be complete and properly formatted with the appropriate number of elements and digits for each element, and the appropriate separator character. Years must be specified as four digits.

  • Date values must be within a valid range. Years: 0001 through 9999. Months: 1 through 12. Days: 1 through 31.

  • The number of days in a month must match the month and year. For example, the date '02–29' is only valid if the specified year is a leap year.

  • Date values less than 10 may include or omit a leading zero. Other non-canonical integer values are not permitted. Therefore, a Day value of '07' or '7' is valid, but '007', '7.0' or '7a' are not valid.

Arguments

date-expression

An expression that is the name of a column, the result of another scalar function, or a date or timestamp literal.

Examples

The following example returns the day of week and week of year for January 2, 2005 (which is a Sunday) and January 1, 2006 (which is a Sunday).

SELECT {fn DAYOFWEEK("2005-1-2")},{fn WEEK("2005-1-2")},
       {fn DAYOFWEEK("2006-1-1")},{fn WEEK("2006-1-1")}

The following examples return the number 9 because the date is the ninth week of the year 2004:

SELECT {fn WEEK('2004-02-25')} AS Wk_Date,
       {fn WEEK('2004-02-25 08:35:22')} AS Wk_Tstamp,
       {fn WEEK(59590)} AS Wk_DInt

The following example returns the number 54 because this particular date is in a leap year that began with Week 2 starting on the second day, as demonstrated by the example immediately following it:

SELECT {fn WEEK('2000-12-31')} AS Week
SELECT {fn WEEK('2000-01-01')}||{fn DAYNAME('2000-01-01')} AS WeekofDay1,
       {fn WEEK('2000-01-02')}||{fn DAYNAME('2000-01-02')} AS WeekofDay2

The following examples all return the current week:

SELECT {fn WEEK({fn NOW()})} AS Wk_Now,
       {fn WEEK(CURRENT_DATE)} AS Wk_CurrD,
       {fn WEEK(CURRENT_TIMESTAMP)} AS Wk_CurrTS,
       {fn WEEK($HOROLOG)} AS Wk_Horolog,
       {fn WEEK($ZTIMESTAMP)} AS Wk_ZTS

The following Embedded SQL example shows the InterSystems IRIS default week of the year and the week of the year with the ISO 8601 standard applied:

TestISO
  SET def=$DATA(^%SYS("sql","sys","week ISO8601"))
  IF def=0 {SET ^%SYS("sql","sys","week ISO8601")=0}
  ELSE {SET isoval=^%SYS("sql","sys","week ISO8601")}
     IF isoval=1 {GOTO UnsetISO }
     ELSE {SET isoval=0 GOTO WeekOfYear }
UnsetISO
  SET ^%SYS("sql","sys","week ISO8601")=0
WeekOfYear
  &sql(SELECT {fn WEEK($HOROLOG)} INTO :a)
  WRITE "For Today:",!
  WRITE "default week of year is ",a,!
  SET ^%SYS("sql","sys","week ISO8601")=1
  &sql(SELECT {fn WEEK($HOROLOG)} INTO :b)
  WRITE "ISO8601 week of year is ",b,!
ResetISO
  SET ^%SYS("sql","sys","week ISO8601")=isoval

See Also

FeedbackOpens in a new tab