Skip to main content

DAYOFWEEK (SQL)

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

Synopsis

{fn DAYOFWEEK(date-expression)}

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 InterSystems IRIS 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 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 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:

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

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), an InterSystems IRIS 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: 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.

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 InterSystems IRIS 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.

InterSystems IRIS 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 InterSystems IRIS day of week is undefined or set to the default (7=Sunday), the ISO 8601 standard overrides the InterSystems IRIS default. If InterSystems IRIS day of week is set to any other value, it overrides week ISO8601 for DAYOFWEEK. See example below.

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.

Examples

In the following example, both select-items return the number 5 (if Sunday is set as the first day of the week) because the specified date-expression (64701 = February 22, 2018) is a Thursday:

SELECT {fn DAYOFWEEK('2018-02-22')}||' '||DATENAME('dw','2018-02-22') AS ODBCDoW,
       {fn DAYOFWEEK(64701)}||' '||DATENAME('dw','64701') 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 the %SYS or USER namespaces. If you do, this program aborts to prevent changing these settings.

SetUp
  SET TestNsp="USER"
  SET ControlNsp="%SYS"
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()
  SET $NAMESPACE=TestNsp
  &sql(SELECT {fn DAYOFWEEK($HOROLOG)} INTO :a)
  WRITE "Today is the ",a," day of week in ",$NAMESPACE,!
  SET $NAMESPACE=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 InterSystems IRIS 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

FeedbackOpens in a new tab