tomonth(X;Y)

Convert a string, date+time, or date to a date value in the form MM. (Available as of version 18.02)

Syntax

tomonth(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 also add an optional argument to tell toyear 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. For tomonth(X;Y), 'allowyqm' is on by default.
  • 'forbidyqm' - Use if you do not want to use the default 'allowyqm' and want to require complete dates only.

tomonth(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 in the form MM that can be expressed in a date format (such as type:date). 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, tomonth(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 a year in the form MM. 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_tomonth" value="tomonth(date_text;'mdymode')"/>

The first date, 31 Oct 2017, is parsed correctly because there is no ambiguity about "Oct" being the month. 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 todate 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 the MM format 10 (October, the beginning of Q4).