`todatetime(X;Y)`

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

Syntax

todatetime(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 `todatetime` how to handle invalid input. Valid values are: `'invalidna'` - Return null on invalid input. This is the default value. `'invaliderr'` - Return an error on invalid input. `'besteffort'` - Allows 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 todatetime how to handle invalid input.

Valid values are:

'invalidna' - Return null on invalid input. This is the default value.
'invaliderr' - Return an error on invalid input.
'besteffort' - Allows 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.

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

You can use todatetime(X;Y) to convert Unix epoch time to date+time. You need to divide the Unix epoch time (which is in seconds) by 86400 to get the number of days for date+time. Then you subtract 23741 to adjust the offset (The Unix epoch starts at January 1, 1970, and the k epoch starts at January 1, 2035).

Note: As of version 18.04, you can use the function dtunix(X;Y) to convert Unix epoch time to date+time without having to divide and subtract.

Return Value

Returns a signed float value in the form XXXX.YYYYYYYYYYY, that can be expressed in a date+time format (such as type:datehms24). If X is a date only, datetime(X;Y) expands the date with a time of 00:00:00 (midnight).

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, todatetime(X;Y) provides date validation if the input value is already in a date or date+time format.

The date+time format uses 1/1/2035 as the epoch (or reference) point. Specifically, the value XXXX.YYYYYYYYYYY is a signed float representing the number of days before or after the epoch. This value is typically negative, because the date+time is usually before the epoch. For more information about date formats, see Dates and time.

Example

The following example converts strings containing dates to the date format datehms24. The Y argument is 'dmymode', so that any ambiguous dates are parsed as day/month/year.

<table cols="date_time_text">
"31 Oct 2017 12:15:10"
"February 3, 2016 04:45:01"
"1/2/5 13:26:05"
"3/12/15" 
</table>

<willbe name="date_time_converted" value="todatetime(date_time_text;'mdymode')" format="type:datehms24"/>

The first date, 31 Oct 2017 12:15:10, is parsed correctly because there is no ambiguity about "Oct" being the month. The second date, February 3, 2016 04:45:01, is parsed with 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, 3/12/15, contains no time, so todatetime(X;Y) adds a timestamp of 00:00:00.

Example: Converting Unix epoch time to `date+time`

The following example shows how to convert Unix epoch time to the 1010data date+time format by using todatetime().

<table cols="unix_epoch">
1607554906
1609556010
1630605577
</table>

<willbe name="date_time_converted" value="todatetime(unix_epoch/86400-23741;)"
format="type:datehms12"/>