Skip to main content


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


{fn DAYOFWEEK(date-expression)}


Argument Description
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.


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:

  • 1 — Sunday

  • 2 — Monday

  • 3 — Tuesday

  • 4 — Wednesday

  • 5 — Thursday

  • 6 — Friday

  • 7 — Saturday

The first day of the week default can be overridden 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.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 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()Opens in a new tab method call:


Date Validation

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

  • A valid date-expression may consist of a date string (yyyy-mm-dd), a date and time string (yyyy-mm-dd hh:mm:ss), a Caché date integer, or a $HOROLOG value. DAYOFWEEK evaluates only the date portion of the date-expression.

  • 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 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.

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.


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:

       {fn DAYOFWEEK(CURRENT_DATE)} AS DoW_CurrDate,
       {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.


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.

  SET TestNsp="USER"
  SET ControlNsp="%SYS"
  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")
  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()
  SET ^%SYS("sql","sys","day of week",TestNsp)=3
  WRITE TestNsp," namespace DoW set",!
  DO TestDayofWeek()
  SET ^%SYS("sql","sys","day of week")=2
  WRITE "Systemwide DoW set with ",TestNsp," DoW set",!
  DO TestDayofWeek
  KILL ^%SYS("sql","sys","day of week",TestNsp)
  WRITE "Namespace ",TestNsp," DoW killed",!
  DO TestDayofWeek
  SET ^%SYS("sql","sys","day of week")=initialDoW
  WRITE "Systemwide DoW reset after ",TestNsp," DoW killed",!
  DO TestDayofWeek
  WRITE "Today is the ",a," day of week in ",$NAMESPACE,!
  WRITE "Today is the ",b," day of week in ",$NAMESPACE,!!

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:

  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 }
  SET ^%SYS("sql","sys","week ISO8601")=0
  WRITE "Today:",!
  WRITE "default day of week is ",a,!
  SET ^%SYS("sql","sys","week ISO8601")=1
  WRITE "ISO8601 day of week is ",b,!
  SET ^%SYS("sql","sys","week ISO8601")=isoval

See Also

FeedbackOpens in a new tab