Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

...

For more information on formatting numeric types, see NUMFORMAT Function.

D s
snippetBasic

DATEFORMAT
d-codelang-syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value:
dateformat(MyDate, 'yyyy-MM-dd') as: 'dateformat_MyDate'

dateformat(MyDate, 'yyyy-MM-dd')


Output:
Generates a column of Returns the valid date values in the MyDate column converted to year-month-day format.

D s
snippetSyntax

DATEFORMAT
d-codelang-syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextderive type:single value:
dateformat(Datetime_col, date_format_string)

dateformat(Datetime_col, date_format_string)


ArgumentRequired?Data TypeDescription
Datetime_colYdatetimeName of column containing date values to be formatted
date_format_stringYstringString literal identifying the date format to apply to the value

...

MyDate
2/1/00 9:20
4/5/10 11:25
6/7/99 22:00
12/20/1894 15:45:00
13/7/1999 22:00:00

TransformTransformation:

When this data is loaded into the application, it is not immediately recognized as a Datetime column, as the variation among the data complicates deciding on the proper date format. The first three rows look to be in a consistent format, but the other two are problematic.

...

Info

NOTE: Do not add this transform at this time. It is strictly used for reviewing the effects on data quality.

d-

...

trans
RawWrangletrue
p03Value'mm-dd-yy hh:mm:ss','mm*dd*yy*HH:MM'
Typestep
WrangleTextsettype col: MyDate type: 'Datetime','mm-dd-yy hh:mm:ss','mm*dd*yy*HH:MM'
p01NameColumns
p01ValueMyDate
p02NameNew type
p02ValueCustom or Date/Time
p03NameSpecify type
SearchTermChange column data type

When the column is reformatted, you should notice that the last two values in the column are mismatched. In the column histogram, you can see that date ranges include the 1999 date in the third row, so the final row should work if it was a valid date.

The 1894 value looks like an outlier value and could be removed:

d-

...

trans
RawWrangletrue
p03Valuematches([MyDate], `12/20/1894`)
Typestep
WrangleTextdelete row:

...

matches([MyDate], `12/20/1894`)
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueDelete matching rows
p04NameAction
SearchTermFilter rows

For the remaining 1999 row, you can delete it or use the following transforms to conform it to the other rows. Use the following transform to change the 13 month value to a 12:

d-

...

trans
RawWrangletrue
p03Value'12\/'
Typestep
WrangleTextreplace col: MyDate on: `13/` with: '12\/' global: true
p01NameColumn
p01ValueMyDate
p02NameFind
p02Value`13/`
p03NameReplace with
p04Valuetrue
p04NameMatch all occurrences
SearchTermReplace text or pattern

The following two transforms complete the cleanup steps:

d-

...

trans
RawWrangletrue
p03Value'\/99'
Typestep
WrangleTextreplace col: MyDate on: `/1999` with: '\/99' global: true

...

p01NameColumn
p01ValueMyDate
p02NameFind
p02Value`/1999`
p03NameReplace with
p04Valuetrue
p04NameMatch all occurrences
SearchTermReplace text or pattern

D trans
RawWrangletrue
p03Value':00'
Typestep
WrangleTextreplace col: MyDate on: `:#+:00` with: ':00' global: true
p01NameColumn
p01ValueMyDate
p02NameFind
p02Value`:#+:00`
p03NameReplace with
p04Valuetrue
p04NameMatch all occurrences
SearchTermReplace text or pattern

If you apply the original formatting step, all dates are valid:

d-

...

trans
RawWrangletrue
p03Value'mm-dd-yy hh:mm:ss','mm*dd*yy*HH:MM'
Typestep
WrangleTextsettype col: MyDate type: 'Datetime','mm-dd-yy hh:mm:ss','mm*dd*yy*HH:MM'
p01NameColumns
p01ValueMyDate
p02NameNew type
p02ValueCustom or Date/Time
p03NameSpecify type
SearchTermChange column data type

Now, your Datetime column can be formatted as needed using the dateformat function. The following step generates a new column that contains year, month, and day information as a single numeric value:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextderive type:single value:

...

dateformat(MyDate, 'yyyyMMdd')
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateformat(MyDate, 'yyyyMMdd')
SearchTermNew formula

Results:

The final dataset should look like the following:

...

Example - Other date formatting variations

Numeric date, year first

SourceTransformTransformationResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

d-

code

trans
RawWrangletrue
p03Value'newTimestamp'
Typestep
WrangleTextderive type:single value:

DATEFORMAT

dateformat(Timestamp,'yyyy-MM-dd') as:'newTimestamp'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateformat(Timestamp,'yyyy-MM-dd')
p03NameNew column name
SearchTermNew formula

2016-02-15

1999-03-12

2011-11-21

Numeric date, American style

SourceTransformTransformationResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

d-

code

trans
RawWrangletrue
p03Value'newTimestamp'
Typestep
WrangleTextderive type:single value:

DATEFORMAT

dateformat(Timestamp,'M/d/yy') as:'newTimestamp'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateformat(Timestamp,'M/d/yy')
p03NameNew column name
SearchTermNew formula

2/15/16

3/12/99

11/21/11

Full written date

SourceTransformTransformationResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

d-

code

trans
RawWrangletrue
p03Value'newTimestamp'
Typestep
WrangleTextderive type:single value:

DATEFORMAT

dateformat(Timestamp,'MMMM dd, yyyy') as:'newTimestamp'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateformat(Timestamp,'MMMM dd, yyyy')
p03NameNew column name
SearchTermNew formula

February 15, 2016

March 12, 1999

November 21, 2011

Abbreviated date,  including abbreviated day including abbreviated day of week

SourceTransformTransformationResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

d-

code

trans
RawWrangletrue
p03Value'newTimestamp'
Typestep
WrangleTextderive type:single value:

DATEFORMAT

dateformat(Timestamp,'EEE MMM dd, yyyy') as:'newTimestamp'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateformat(Timestamp,'EEE MMM dd, yyyy')
p03NameNew column name
SearchTermNew formula

Mon Feb 15, 2016

Fri Mar 12, 1999

Mon Nov 21, 2011

Full 24-hour time

SourceTransformTransformationResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

d-

code

trans
RawWrangletrue
p03Value'newTimestamp'
Typestep
WrangleTextderive type:single value:

DATEFORMAT

dateformat(Timestamp,'HH:mm:ss.SSS') as:'newTimestamp'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateformat(Timestamp,'HH:mm:ss.SSS')
p03NameNew column name
SearchTermNew formula

13:26:58.123

2:45:21.456

23:02:18.000

Twelve-hour time with AM/PM indicator


Source
Transform
TransformationResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

d-

code

trans
RawWrangletrue
p03Value'newTimestamp'
Typestep
WrangleTextderive type:single value:

DATEFORMAT

dateformat(Timestamp,'h:mm:ss a') as:'newTimestamp'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateformat(Timestamp,'h:mm:ss a')
p03NameNew column name
SearchTermNew 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

For more information on supported date formats, see Datetime Data Type.

...