Caché SQL Reference
WEEK
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A date function that returns the week of the year as an integer for a date expression.
Synopsis
{fn WEEK(date-expression)}
Arguments
date-expression An expression that is the name of a column, the result of another scalar function, or a date or timestamp literal.
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 (number of days since 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.
Caché also supports the ISO 8601 standard for determining the week of the year. This standard is principally used in European countries. When Caché 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 a Caché date integer, a $HOROLOG or $ZTIMESTAMP value, an ODBC format date string, or a timestamp.
A date-expression timestamp is data type %Library.TimeStamp (yyyy-mm-dd hh:mm:ss.fff).
The time portion of the timestamp is not evaluated and can be omitted. The date-expression can also be specified as data type %Library.FilemanDate, %Library.FilemanTimestamp, or %MV.Date.
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() method call:
$SYSTEM.SQL.WEEK(date-expression)
Date Validation
WEEK performs the following checks on input values. If a value fails a check, the null string is returned.
Examples
The following Embedded SQL 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).
  SET x="2005-1-2"
  SET y="2006-1-1"
  &sql(SELECT {fn DAYOFWEEK(:x)},{fn WEEK(:x)},
       {fn DAYOFWEEK(:y)},{fn WEEK(:y)}
  INTO :a,:b,:c,:d)
  IF SQLCODE'=0 {
    WRITE !,"Error code ",SQLCODE }
  ELSE {
    WRITE !,"2005 Day of Week is: ",a," (Sunday=1)"
    WRITE " Week of Year is: ",b
    WRITE !,"2006 Day of Week is: ",c," (Sunday=1)"
    WRITE " Week of Year is: ",d }
 
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 Caché 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