toquarter(X;Y)

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

Syntax

toquarter(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 toquarter 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 toquarter(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.

toquarter(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 Q. 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, toquarter(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 Q. 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_toquarter" value="toquarter(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 toquarter 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 Q format 4.