WEEK (SQL)
Synopsis
{fn WEEK(dateexpression)}
Arguments
Argument  Description 

dateexpression  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 dateexpression, 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 sevenday 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 dateexpression can be a Caché date integer, a $HOROLOG or $ZTIMESTAMP value, an ODBC format date string, or a timestamp.
A dateexpression timestamp is data type %Library.TimeStampOpens in a new tab (yyyymmdd hh:mm:ss.fff).
The time portion of the timestamp is not evaluated and can be omitted. The dateexpression 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()Opens in a new tab method call:
$SYSTEM.SQL.WEEK(dateexpression)
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: 1841 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 noncanonical integer values are not permitted. Therefore, a Day value of '07' or '7' is valid, but '007', '7.0' or '7a' are not valid.
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="200512" SET y="200611" &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('20040225')} AS Wk_Date, {fn WEEK('20040225 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('20001231')} AS Week
SELECT {fn WEEK('20000101')}{fn DAYNAME('20000101')} AS WeekofDay1, {fn WEEK('20000102')}{fn DAYNAME('20000102')} 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