todayofwk(X;Y)

Convert a string, date+time, or date to a value corresponding to the day of the week. (Available as of version 18.02)

Syntax

todayofwk(X;Y)

Input

Argument Type Description
X any A scalar value or the name of a column containing date-related values
Valid values can be of the form:
  • string
  • date+time
  • date
Y text An optional argument specifying which date mode to use if the date has an ambiguous format. For example, the string "1/2/3" can be interpreted as January 2, 2003 (using month/day/year format), or it can be interpreted as February 3, 2001 (using year/month/day format).

Valid values are:

  • 'mdymode' - Interpret ambiguities as month/day/year
  • 'dmymode' - Interpret ambiguities as day/month/year
  • 'ymdmode' - Interpret ambiguities as year/month/day

The default value is 'mdymode'.

You can add the argument 'monday' to begin the week on Monday (Monday is 1, Tuesday is 2, etc.) to be compatible with the ISO-8601 standard. By default, the beginning of the week is Sunday (Sunday is 1, Monday is 2, etc.)

You can also add an optional argument to tell todayofwk how to handle invalid input.

Valid values are:
  • 'invalidna' - Return null on invalid input. This is the default value.
  • 'invaliderr' - Throw an error on invalid input.
  • 'besteffort' - Allow a string that is technically not valid (such as containing invalid trailing characters) to be converted. If'besteffort' is unsuccessful, the function returns null.
  • 'allowyqm' - Accept year (2021), quarter (20214), and month (202112) integer values. Without 'allowyqm', integer values are always construed as dates. This option will also allow strings that unambiguously specify a year ('2021'), a quarter ('2021Q4' or '4Q2021'), or a month ('12/2021'). Year values are treated as January 1 of the year, quarters as the first day of the quarter (such as April 1), and months as the first day of the month.
  • 'forbidyqm' - Use if you want to require complete dates only.

todayofwk(X;Y) can take a variety of input values and uses internal logic to parse the dates. For example, the function assumes that a 4-digit number represents a year, and that a month name or abbreviation (such as "Jan") is obviously a month. Any ambiguities are resolved using the setting of the Y argument.

Return Value

Returns an integer value corresponding to the day of the week. If X contains a time stamp, it is discarded.

If the input is invalid and the Y argument includes 'invalidna', the function returns a null value. If the input is invalid and the Y argument includes 'invaliderr', the function throws an error. In this way, todayofwk(X;Y) provides date validation if the input value is already in a date or date+time format.

For more information about date formats, see Dates and time.

Example

The following example converts strings containing dates in various formats to an integer corresponding to a day of the week. The Y argument is 'mdymode', so that any ambiguous dates are parsed as month/day/year.

<table cols="date_text">
"31 Oct 2017"
"February 3, 2016 04:45:01"
"1/2/5"
"15/3/12" 
"2021Q4"
</table>

<willbe name="date_todayofwk" value="todayofwk(date_text;'mdymode' 'allowyqm')"/>

The first date, 31 Oct 2017, is parsed correctly because there is no ambiguity about "Oct" being the month and 31 being the day. The second date, February 3, 2016 04:45:01, is parsed after dropping the time stamp. The third date, 1/2/5, is ambiguous, so the Y argument tells todayofwk to parse the date as month/day/year, or 01/02/2005. 15/3/12, cannot be parsed as month/day/year because it is not a valid date, and returns a null value. 2021Q4 is converted to 6 (October 1, the beginning of Q4, occurs on a Friday), because we used the 'allowyqm' option.