DATEFORMAT Function

...

Missing values for this function in the source data result in missing values in the output.
Multiple columns and wildcards are not supported.

D s

snippet	usage

Required?	Data Type	Example Value
Yes	Datetime	`myDate`

...

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

...

Page tree

Versions Compared

Old Version 3

New Version Current

Key

Example - Other date formatting variations

Numeric date, year first

Numeric date, American style

Full written date

Abbreviated date, including abbreviated day of week

Full 24-hour time

Twelve-hour time with AM/PM indicator