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

A date function that returns the day of the week as an integer for a date expression.
Synopsis
{fn DAYOFWEEK(date-expression)}
Arguments
date-expression A valid ODBC-format date or $HOROLOG format date, with or without the time component. An expression that is the name of a column, the result of another scalar function, or a date or timestamp literal.
Description
DAYOFWEEK takes a date-expression and returns an integer corresponding to the day of the week for that date. Days of the week are counted from the first day of the week; the Caché default is that Sunday is the first day of the week. Therefore, by default, the returned values represent these days:
The first day of the week default can be overriden system-wide or for specific namespaces, as described in Setting First Day of Week.
Note that the ObjectScript $ZDATE and $ZDATETIME functions count days of the week from 0 through 6 (not 1 through 7).
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 day of week information can be returned by using the DATEPART or TO_DATE function. To return the name of the day of the week, use DAYNAME, DATENAME, or TO_DATE.
This function can also be invoked from ObjectScript using the DAYOFWEEK() method call:
$SYSTEM.SQL.DAYOFWEEK(date-expression)
Date Validation
DAYOFWEEK performs the following checks on input values. If a value fails a check, the null string is returned.
Setting First Day of Week
By default, the first day of the week is Sunday. You can override this default system-wide by specifying SET ^%SYS("sql","sys","day of week")=n, where n values are 1=Monday through 7=Sunday. To set Monday as the first day of the week specify SET ^%SYS("sql","sys","day of week")=1. If Monday is the first day of the week, a Wednesday date-expression returns 3, rather than the 4 that would be returned if Sunday was the first day of the week. To reset the Caché default (Sunday as first day of week), specify SET ^%SYS("sql","sys","day of week")=7.
You can set the first day of the week for a specific namespace by specifying SET ^%SYS("sql","sys","day of week",namespace)=n, where n values are 1=Monday through 7=Sunday. To set Monday as the first day of the week for the USER namespace, specify SET ^%SYS("sql","sys","day of week","USER")=1. Once the first day of the week is set at the namespace level, changing the system-wide setting by specifying SET ^%SYS("sql","sys","day of week")=n has no effect on that namespace. To restore the ability to change that namespace’s first day of week default, you must kill ^%SYS("sql","sys","day of week",namespace). See example below.
Caché also supports the ISO 8601 standard for determining the day of the week, week of the year, and other date settings. This standard is principally used in European countries. The ISO 8601 standard begins counting the days of the week with Monday. To activate ISO 8601, SET ^%SYS("sql","sys","week ISO8601")=1; to deactivate, set it to 0. If week ISO8601 is activated and Caché day of week is undefined or set to the default (7=Sunday), the ISO 8601 standard overrides the Caché default. If Caché day of week is set to any other value, it overrides week ISO8601 for DAYOFWEEK. See example below.
Examples
In the following example, both select-items return the number 6 (if Sunday is set as the first day of the week) because the specified date-expression (63876 = November 20, 2015) is a Friday:
SELECT {fn DAYOFWEEK('2015-11-20')}||' '||DATENAME('dw','2015-11-20') AS ODBCDoW,
       {fn DAYOFWEEK(63876)}||' '||DATENAME('dw','63876') AS HorologDoW
 
In the following example, all select-items return the integer corresponding to the current day of the week:
SELECT {fn DAYOFWEEK({fn NOW()})} AS DoW_Now,
       {fn DAYOFWEEK(CURRENT_DATE)} AS DoW_CurrDate,
       {fn DAYOFWEEK(CURRENT_TIMESTAMP)} AS DoW_CurrTstamp,
       {fn DAYOFWEEK($ZTIMESTAMP)} AS DoW_ZTstamp,
       {fn DAYOFWEEK($HOROLOG)} AS DoW_Horolog
 
Note that $ZTIMESTAMP returns Coordinated Universal Time (UTC). The other time-expression values return the local time. This may affect the DAYOFWEEK value.
The following Embedded SQL example demonstrates changing the first day of week for a namespace. It initially sets the system-wide first day of week (to 7), then sets the first day of week for a namespace (to 3). A subsequent system-wide first day of week change (to 2) has no effect on namespace first day of week until the program kills the namespace-specific setting. Killing the namespace-specific setting immediately resets that namespace’s first day of week to the current system-wide value. Finally, the program restores the initial system-wide setting.
Note:
The following program tests if you have namespace-specific first day of week settings for SAMPLES or USER. If you do, this program aborts to prevent changing these settings.
SetUp
  SET TestNsp="SAMPLES"
  SET ControlNsp="USER"
InitialDoWValues
  WRITE "Systemwide default DoW initial values",!
  DO TestDayofWeek()
  IF a=b {WRITE "No namespace-specific DoW defaults",!!}
  ELSE {WRITE "DoW initial settings are namespace-specific",!
        WRITE "Stopping this program"
        QUIT }
  SET initialDoW=^%SYS("sql","sys","day of week")
SetSystemwideDoW
  KILL ^%SYS("sql","sys","day of week",TestNsp)
  KILL ^%SYS("sql","sys","day of week",ControlNsp)
  SET ^%SYS("sql","sys","day of week")=7
  WRITE "Systemwide DoW set",!
  DO TestDayofWeek()
SetNamespaceDoW
  SET ^%SYS("sql","sys","day of week",TestNsp)=3
  WRITE TestNsp," namespace DoW set",!
  &sql(SELECT {fn DAYOFWEEK($HOROLOG)} INTO :a)
  DO TestDayofWeek()
ResetSystemwideDoW
  SET ^%SYS("sql","sys","day of week")=2
  WRITE "Systemwide DoW set with ",TestNsp," DoW set",!
  DO TestDayofWeek
KillNamespaceDoW
  KILL ^%SYS("sql","sys","day of week",TestNsp)
  WRITE "Namespace ",TestNsp," DoW killed",!
  DO TestDayofWeek
ResetSystemwideDoWDefault
  SET ^%SYS("sql","sys","day of week")=initialDoW
  WRITE "Systemwide DoW reset after ",TestNsp," DoW killed",!
  DO TestDayofWeek
TestDayofWeek()
  ZNSPACE TestNsp
  &sql(SELECT {fn DAYOFWEEK($HOROLOG)} INTO :a)
  WRITE "Today is the ",a," day of week in ",$NAMESPACE,!
  ZNSPACE ControlNsp
  &sql(SELECT {fn DAYOFWEEK($HOROLOG)} INTO :b)
  WRITE "Today is the ",b," day of week in ",$NAMESPACE,!!
  RETURN
 
The following Embedded SQL example shows the default day of the week and the day of the week with the ISO 8601 standard applied. It assumes that the Caché day of week is undefined or set to the default:
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 DayofWeek }
UnsetISO
  SET ^%SYS("sql","sys","week ISO8601")=0
DayofWeek
  &sql(SELECT {fn DAYOFWEEK($HOROLOG)} INTO :a)
  WRITE "Today:",!
  WRITE "default day of week is ",a,!
  SET ^%SYS("sql","sys","week ISO8601")=1
  &sql(SELECT {fn DAYOFWEEK($HOROLOG)} INTO :b)
  WRITE "ISO8601 day of week is ",b,!
ResetISO
  SET ^%SYS("sql","sys","week ISO8601")=isoval
 
See Also