Skip to main content

DATETIME Function

Generates a Datetime value from the following inputs of Integer type: year, month, day, hour, minute, and second.

  • Source values can be Integer literals or column references to values that can be inferred as Integers.

  • If any of the source values are invalid or out of range, a missing value is returned.

  • This function must be nested within another function that accepts date values, such as DATEFORMAT, as arguments. See the example below.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

Integer literal values:

dateformat(datetime(2015,02,15, 2, 4, 0),'yyyy-MM-dd:HH:mm:ss')

Output: Returns the formatted date value: 2015-02-15:02:04:00.

Column reference values:

dateformat(datetime(myYear, myMonth, myDay, myHour, myMin, mySec),'MMM yyyy - HH:MM:SS')

Output: Generates date values where:

  • MMM = short value for myMonth

  • yyyy = value from myYear

  • HH = value from myHour

  • MM = value from myMin

  • SS = value from mySec

Syntax and Arguments

dateformat(datetime(year_integer_col,month_integer_col,day_Integer_col, hour_Integer_col, minute_Integer_col,second_Integer_col ),'date_format_string')

Argument

Required?

Data Type

Description

year_integer_col

Y

integer

Name of column or Integer literal representing the year value to apply to the function

month_integer_col

Y

integer

Name of column or Integer literal representing the month value to apply to the function

day_integer_col

Y

integer

Name of column or Integer literal representing the day value to apply to the function

hour_integer_col

Y

integer

Name of column or Integer literal representing the hour value to apply to the function

minute_integer_col

Y

integer

Name of column or Integer literal representing the minute value to apply to the function

second_integer_col

Y

integer

Name of column or Integer literal representing the day second to apply to the function

date_format_string

Y

string

String literal identifying the date format to apply to the value

For more information on syntax standards, see Language Documentation Syntax Notes.

year_integer_col

Integer literal or name of the column containing integer values for the year.

  • Missing values for this function in the source data result in missing values in the output.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

Integer (literal or column reference)

2015

month_integer_col

Integer literal or name of the column containing integer values for the month.

  • Values must be 1 or more, with a maximum value of 12.

  • Missing values for this function in the source data result in missing values in the output.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

Integer (literal or column reference)

4

day_integer_col

Integer literal or name of the column containing integer values for the day.

  • Values must be 1 or more, with a maximum value for any month of 31.

  • Missing values for this function in the source data result in missing values in the output.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

Integer (literal or column reference)

26

hour_integer_col

Integer literal or name of the column containing integer values for the hour.

  • Values must be 0 or more, with a maximum value for any hour of 23.

  • Missing values for this function in the source data result in missing values in the output.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

Integer (literal or column reference)

21

minute_integer_col

Integer literal or name of the column containing integer values for the minute.

  • Values must be 0 or more, with a maximum value for any minute of 59.

  • Missing values for this function in the source data result in missing values in the output.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

Integer (literal or column reference)

04

second_integer_col

Integer literal or name of the column containing integer values for the second.

  • Values must be 0 or more, with a maximum value for any second of 59.

  • Missing values for this function in the source data result in missing values in the output.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

Integer (literal or column reference)

15

date_format_string

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

Designer Cloud Powered by Trifacta Enterprise Edition 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.

  • Missing values for this function in the source data result in missing values in the output.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

String

'MM/dd/yyyy'

Examples

Tip

For additional examples, see Common Tasks.

Example - date and time functions

This example provides an overview on various date and time functions.

Functions:

Item

Description

DATE Function

Generates a date value from three inputs of Integer type: year, month, and day.

TIME Function

Generates time values from three inputs of Integer type: hour, minute, and second.

DATETIME Function

Generates a Datetime value from the following inputs of Integer type: year, month, day, hour, minute, and second.

DATEFORMAT Function

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.

Source:

year

month

day

hour

minute

second

2016

10

11

2

3

0

2015

11

20

15

22

30

2014

12

25

18

30

45

Transformation:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

DATEFORMAT(DATE (year, month, day),'yyyy-MM-dd')

Parameter: New column name

'fctn_date'

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

DATEFORMAT(TIME (hour, minute, second),'HH-mm-ss')

Parameter: New column name

'fctn_time'

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

DATEFORMAT(DATETIME (year, month, day, hour, minute, second),'yyyy-MM-dd-HH:mm:ss')

Parameter: New column name

'fctn_datetime'

Results:

Note

All inputs must be inferred as Integer type and must be valid values for the specified input. For example, month values must be integers between 1 and 12, inclusive.

year

month

day

hour

minute

second

fctn_date

fctn_time

fctn_datetime

2016

10

11

2

3

0

2016-10-11

02-03-00

2016-10-11-02:03:00

2015

11

20

15

22

30

2015-11-20

15-22-30

2015-11-20-15:22:30

2014

12

25

18

30

45

2014-12-25

18-30-45

2014-12-25-18:30:45

You can apply other date and time functions to the generated columns. For an example, see YEAR Function.