...
- Missing values for this function in the source data result in missing values in the output.
- Multiple columns and wildcards are not supported.
| Required? | Data Type | Example Value |
|---|
| Yes | Datetime | myDate |
...
supports Java formatting strings, with some exceptions
.| Info |
|---|
NOTE: Two-digit values for the year that are older than 80 years from the current year are forward-ported into the future. For example, in a job run on Dec 31, 2021, the date 01/01/41 is interpreted as 01/01/1941. However, if the job is run the next day (January 01, 2022), then the same data is interpreted as 01/01/2041. For more information including workarounds, see Datetime Data Type. |
| Info |
|---|
NOTE: If the platform cannot recognize the date format string, the generated result is written as a string value. |
...
Example - Other date formatting variations
Numeric date, year first
| Source | Transformation | Results |
|---|
2/15/16 13:26:58.123 3/12/99 2:45:21.456 11/21/11 23:02:18.000 | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'newTimestamp' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:dateformat(Timestamp,'yyyy-MM-dd') as:'newTimestamp' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | dateformat(Timestamp,'yyyy-MM-dd') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| 2016-02-15 1999-03-12 2011-11-21 |
Numeric date, American style
| Source | Transformation | Results |
|---|
2/15/16 13:26:58.123 3/12/99 2:45:21.456 11/21/11 23:02:18.000 | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'newTimestamp' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:dateformat(Timestamp,'M/d/yy') as:'newTimestamp' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | dateformat(Timestamp,'M/d/yy') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| 2/15/16 3/12/99 11/21/11 |
Full written date
| Source | Transformation | Results |
|---|
2/15/16 13:26:58.123 3/12/99 2:45:21.456 11/21/11 23:02:18.000 | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'newTimestamp' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:dateformat(Timestamp,'MMMM dd, yyyy') as:'newTimestamp' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | dateformat(Timestamp,'MMMM dd, yyyy') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| February 15, 2016 March 12, 1999 November 21, 2011 |
Abbreviated date, including abbreviated day of week
| Source | Transformation | Results |
|---|
2/15/16 13:26:58.123 3/12/99 2:45:21.456 11/21/11 23:02:18.000 | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'newTimestamp' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:dateformat(Timestamp,'EEE MMM dd, yyyy') as:'newTimestamp' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | dateformat(Timestamp,'EEE MMM dd, yyyy') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| Mon Feb 15, 2016 Fri Mar 12, 1999 Mon Nov 21, 2011 |
Full 24-hour time
| Source | Transformation | Results |
|---|
2/15/16 13:26:58.123 3/12/99 2:45:21.456 11/21/11 23:02:18.000 | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'newTimestamp' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:dateformat(Timestamp,'HH:mm:ss.SSS') as:'newTimestamp' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | dateformat(Timestamp,'HH:mm:ss.SSS') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| 13:26:58.123 2:45:21.456 23:02:18.000 |
Twelve-hour time with AM/PM indicator
...
| Source | Transformation | Results |
|---|
2/15/16 13:26:58.123 3/12/99 2:45:21.456 11/21/11 23:02:18.000 | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'newTimestamp' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:dateformat(Timestamp,'h:mm:ss a') as:'newTimestamp' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | dateformat(Timestamp,'h:mm:ss a') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| Info |
|---|
NOTE: For this function, use of the lower-case hour indicator (h or hh) requires the use of an AM/PM indicator (a). |
| 1:26:58 PM 2:45:21 AM 11:02:18 PM |
...
