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")