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 |
---|---|---|
X |
any | A scalar value or the name of a column containing date-related values Valid values can be of the form:
|
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:
The default value is You can also add an
optional argument to tell Valid values are:
|
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).
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"/>