Excerpt |
---|
Evaluates an input against the default input formats or (if specified) an array of Datetime format strings in their listed order. If the input matches one of the formats, the function outputs a Datetime value. |
- Inputs can be of any type.
- If the input is not a Datetime value and does match one of the specified formats, the output is in the following format:
yyyy-MM-dd HH:mm:ss
. - If the input is a Datetime value and does match, the output is in the input's Datetime format.
After you have converted your strings values to dates, if a sufficient percentage of input strings from a column are successfully converted to one of the matching formats, the column may be retyped as Datetime.
- supports a wide variety of formats for Datetime fields. For more information on supported date formats, see Datetime Data Type.
- You can explore the available Datetime formats through the Transformer page. From a column's type drop-down, select Date/Time . Then, select the formatting category. From the displayed drop-down, you can select a specific format. When this transform step is added to your recipe, you can edit it to see how the format is specified in .
- You can then use the DATEFORMAT function to convert the output values to your preferred Datetime format. See DATEFORMAT Function.
D lang syntax |
---|
RawWrangle | true |
---|
Type | ref |
---|
showNote | true |
---|
WrangleText | derive type:single value: parsedate(strDate, ['yyyy-MM-dd','yyyy-MM','yyyy/MM','yyyy-MM-dd') as: 'convertedDate' |
---|
|
parsedate(strDate, ['yyyy-MM-dd','yyyy-MM','yyyy/MM','yyyy-MM-dd') |
Output: Returns a value structured in yyyy-MM-dd HH:mm:ss
format if the input value in strDate
matches any of default formats, which are the following:
Code Block |
---|
'yyyy-MM-dd HH:mm:ss'
'yyyy/MM/dd HH:mm:ss'
'yyyy-MM-dd'
'yyyy/MM/dd' |
D lang syntax |
---|
RawWrangle | true |
---|
Type | ref |
---|
showNote | true |
---|
WrangleText | derive type:single value: parsedate(strDate, ['yyyy-MM-dd','yyyy-MM','yyyy/MM','yyyy-MM-dd') as: 'convertedDate' |
---|
|
parsedate(strDate, ['yyyy-MM','yyyy/MM',) |
Output: Returns a value structured in yyyy-MM-dd HH:mm:ss
format if the input value in strDate
matches any of the listed formats for dates.
D lang syntax |
---|
RawWrangle | true |
---|
Type | syntax |
---|
showNote | true |
---|
WrangleText | derive type:single value:parsedate(date_col, date_formats_array) |
---|
|
parsedate(date_col, date_formats_array) |
Argument | Required? | Data Type | Description |
---|
date_col | Y | any | Literal, name of a column, or a function returning values to match |
date_formats_array | N | string | (optional) An array of date format strings that are used to match against input values. |
date_col
Literal, column name, or function returning values that are to be evaluated for conversion to Datetime values.
- Inputs values can be of any type.
- Missing values for this function in the source data result in null values in the output.
- Multiple columns and wildcards are not supported.
Required? | Data Type | Example Value |
---|
Yes | any | 'February 24, 2019' |
Array of String values of the date formats to evaluate the inputs.
- When a non-Datetime input value matches one of the date formats in the array, the output is the input value converted to the following format:
Code Block |
---|
yyyy-MM-dd HH:mm:ss |
- Datetime inputs are outputted in their source format.
supports Java formatting strings, with some exceptions.
Info |
---|
NOTE: If the platform cannot recognize the date format string, a null value is written as the output. |
For more information on supported date formats, see Datetime Data Type.
Required? | Data Type | Example Value |
---|
No | Array of Strings | ['yyyy-MM','yyyy/MM'] |
This example illustrates several ways of wrangling heterogeneous date values, including the use of the DATEFORMAT
function.
Source:
Your dataset includes the following messy date values:
MyDateStrings |
---|
2/1/00 14:20 |
4/5/10 11:25 |
6/7/99 22:00 |
13/7/1999 22:00 |
12-20-1894 15:45:00 |
08-12-1956 22:01:04 |
Transformation:
To enable easier comparison in the data grid, you choose to create a new column with the parsed values. From the above, you identify two date formats:
Code Block |
---|
'MM/dd/yy hh:mm'
'MM/dd/YYYY hh:mm:ss' |
Info |
---|
NOTE: Since only one of the above formats matches the default formats, you must specify both in the transformation to perform the proper evalation. |
You create the following transformation to parse them:
D trans |
---|
RawWrangle | true |
---|
p03Value | 'myParsedDates' |
---|
Type | step |
---|
WrangleText | derive type:single value:parsedate(myDateStrings, ['MM/dd/yy hh:mm','MM/dd/YYYY hh:mm:ss']) as:'myParsedDates' |
---|
p01Name | Formula type |
---|
p01Value | Single row formula |
---|
p02Name | Formula |
---|
p02Value | parsedate(myDateStrings, ['MM/dd/yy hh:mm','MM/dd/YYYY hh:mm:ss']) |
---|
p03Name | New column name |
---|
SearchTerm | New formula |
---|
|
When the above step is added to the recipe, the output is as follows:
MyDateStrings | myParsedDates |
---|
2/1/00 14:20 | 2000-02-01 14:20:00 |
4/5/10 11:25 | 2010-04-05 11:25:00 |
6/7/99 22:00 | 1999-06-07 22:00:00 |
13/7/1999 22:00 | 13/7/1999 22:00 |
12-20-1894 15:45:00 | 1894-12-20 15:45:00 |
08-12-1956 22:01:04 | 1956-08-12 22:01:04 |
The output myParsedDates
column is retyped as a Datetime column with one mismatched value: 3/7/1999 22:00
.
This value does not match any of our date formats specified in the array. The solution is to modify the recipe step to include the appropriate format as part of the array:
D trans |
---|
RawWrangle | true |
---|
p03Value | 'myParsedDates' |
---|
Type | step |
---|
WrangleText | derive type:single value:parsedate(myDateStrings, ['MM/dd/yy hh:mm','MM/dd/YYYY hh:mm:ss','dd/MM/yyyy hh:mm']) as:'myParsedDates' |
---|
p01Name | Formula type |
---|
p01Value | Single row formula |
---|
p02Name | Formula |
---|
p02Value | parsedate(myDateStrings, ['MM/dd/yy hh:mm','MM/dd/YYYY hh:mm:ss','dd/MM/yyyy hh:mm']) |
---|
p03Name | New column name |
---|
SearchTerm | New formula |
---|
|
Results:
MyDateStrings | myParsedDates |
---|
2/1/00 14:20 | 2000-02-01 14:20:00 |
4/5/10 11:25 | 2010-04-05 11:25:00 |
6/7/99 22:00 | 1999-06-07 22:00:00 |
13/7/1999 22:00 | 1999-07-13 22:00:00 |
12-20-1894 15:45:00 | 1894-12-20 15:45:00 |
08-12-1956 22:01:04 | 1956-08-12 22:01:04 |
For more information on supported date formats, see Datetime Data Type.
Example - type parsing functions
Include Page |
---|
| EXAMPLE - Type Parsing Functions |
---|
| EXAMPLE - Type Parsing Functions |
---|
|