TO_DATE
Description
TO_DATE
is a row function that converts Text
values to
Date
values, and specifies the format of the date and time elements
in the string.Syntax
TO_DATE
(string_expression
,"date_format
")Return Value
Returns one value per row of type
Date
(which by definition is in UTC).
Input Parameters
- string_expression
- Required. A field or expression of typeText.
- date_format
- Required. A pattern that describes how the date is formatted.
Examples
Define a new
Date
Prism calculated field based on the order_date
base field, which contains timestamps in the format of: 2014.07.10 at 15:08:56
PDT
:TO_DATE([order_date],"yyyy.MM.dd 'at' HH:mm:ss z")
Define a new
Date
Prism calculated field by first combining individual
month
, day
, year
, and depart_time
fields (using
CONCAT
), and performing a transformation on depart_time
to
make sure three-digit times are converted to four-digit times (using
REGEX_REPLACE
):TO_DATE(CONCAT([month],"/",[day],"/",[year],":", REGEX_REPLACE([depart_time],"\b(\d{3})\b","0$1")),"MM/dd/yyyy:HHmm")
Define a new
Date
Prism calculated field based on the created_at
base field, which contains timestamps in the format of: Sat Jan 25 16:35:23 +0800
2014
(this is the timestamp format returned by Twitter's API):TO_DATE([created_at],"EEE MMM dd HH:mm:ss Z yyyy")