Formats a specified Datetime set of values according to the specified date format. Source values can be a reference to a column containing Datetime values.

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

derive type:single value: DATEFORMAT(MyDate, 'yyyy-MM-dd') as: 'dateformat_MyDate'

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

derive type:single value: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

datetime_col

Name of the column whose date data is to be formatted.

 

Required?Data TypeExample Value
YesDatetimemyDate

date_format_string

String value indicating the date format to apply to the input values.

supports Java formatting strings, with some exceptions.

NOTE: If the platform cannot recognize the date format string, the generated result is written as a string value.

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

Required?Data TypeExample Value
YesString'MM/dd/yyyy'

Example - formatting date values

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:

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

Transform:

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.

You can try to change the column to a Datetime type with a format that matches the first three rows. You can select the appropriate format through the type drop-down. When previewed, the transform looks like the following:

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

settype col: MyDate type: 'Datetime','mm-dd-yy hh:mm:ss','mm*dd*yy*HH:MM'

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:

delete row: MATCHES([MyDate], `12/20/1894`)

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:

replace col: MyDate on: `13/` with: '12\/' global: true

The following two transforms complete the cleanup steps:

replace col: MyDate on: `/1999` with: '\/99' global: true

replace col: MyDate on: `:#+:00` with: ':00' global: true

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

settype col: MyDate type: 'Datetime','mm-dd-yy hh:mm:ss','mm*dd*yy*HH:MM'

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:

derive type:single value:DATEFORMAT(MyDate, 'yyyyMMdd')

Results:

The final dataset should look like the following:

MyDatedateformat_MyDate
2/1/00 9:2020000201
4/5/10 11:2520100405
6/7/99 22:0019990607
12/7/99 22:0019991207

Example - Other date formatting variations

Numeric date, year first

SourceTransformResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

derive type:single value:DATEFORMAT(Timestamp,'yyyy-MM-dd') as:'newTimestamp'

2016-02-15

1999-03-12

2011-11-21

Numeric date, American style

SourceTransformResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

derive type:single value:DATEFORMAT(Timestamp,'M/d/yy') as:'newTimestamp'

2/15/16

3/12/99

11/21/11

Full written date

SourceTransformResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

derive type:single value:DATEFORMAT(Timestamp,'MMMM dd, yyyy') as:'newTimestamp'

February 15, 2016

March 12, 1999

November 21, 2011

Abbreviated date, including abbreviated day of week

SourceTransformResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

derive type:single value:DATEFORMAT(Timestamp,'EEE MMM dd, yyyy') as:'newTimestamp'

Mon Feb 15, 2016

Fri Mar 12, 1999

Mon Nov 21, 2011

Full 24-hour time

SourceTransformResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

derive type:single value:DATEFORMAT(Timestamp,'HH:mm:ss.SSS') as:'newTimestamp'

13:26:58.123

2:45:21.456

23:02:18.000

Twelve-hour time with AM/PM indicator


SourceTransformResults

2/15/16 13:26:58.123

3/12/99 2:45:21.456

11/21/11 23:02:18.000

derive type:single value:DATEFORMAT(Timestamp,'h:mm:ss a') as:'newTimestamp'

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.