`todate(X;Y)`

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

Syntax

todate(X;Y)

Input

Argument Type Description

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 `todate` 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'` - If specified, 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.

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 todate 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' - If specified, 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.

todate(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 YYYYMMDD 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, todate(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 the date format date4y. 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" 
</table>

<willbe name="date_converted" value="todate(date_text;'mdymode')" format="type:date4y"/>

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. The last date, 15/3/12, cannot be parsed as month/day/year because it is not a valid date, and returns a null value.