Page tree



Contents:

The cloud-based version of Trifacta Wrangler is now available! Read all about it, and register for your free account.

Contents:


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.

Basic Usage

Integer literal values:


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

Column reference values:


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


ArgumentRequired?Data TypeDescription
year_integer_colYintegerName of column or Integer literal representing the year value to apply to the function
month_integer_colYintegerName of column or Integer literal representing the month value to apply to the function
day_integer_colYintegerName of column or Integer literal representing the day value to apply to the function
hour_integer_colYintegerName of column or Integer literal representing the hour value to apply to the function
minute_integer_colYintegerName of column or Integer literal representing the minute value to apply to the function
second_integer_colYintegerName of column or Integer literal representing the day second to apply to the function
date_format_stringYstringString 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 TypeExample Value
YesInteger (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 TypeExample Value
YesInteger (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 TypeExample Value
YesInteger (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 TypeExample Value
YesInteger (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 TypeExample Value
YesInteger (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 TypeExample Value
YesInteger (literal or column reference)15

date_format_string

For more information on supported data formatting strings, see Supported Data Types.

For more information, see DATEFORMAT Function.

Examples

Example - date and time functions

This example illustrates how the DATE and TIME functions operate. Both functions require that their outputs be formatted properly using the DATEFORMAT function.

  • DATE - Generates valid Datetime values from three integer inputs: year, month, and day. See DATE Function.
  • TIME - Generates valid Datetime values from three integer inputs: hour, minute, and second. See TIME Function.
  • DATETIME - Generates valid Datetime values from six integer inputs: year, month, day, hour, minute, and second. See DATETIME Function.
  • DATEFORMAT - Formats valid Datetime values according to the provided formatting string. See DATEFORMAT Function.

Source:

yearmonthdayhourminutesecond
20161011230
20151120152230
20141225183045

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.

yearmonthdayhourminutesecondfctn_datefctn_timefctn_datetime
201610112302016-10-1102-03-002016-10-11-02:03:00
201511201522302015-11-2015-22-302015-11-20-15:22:30
201412251830452014-12-2518-30-452014-12-25-18:30:45

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

Your Rating: Results: 1 Star2 Star3 Star4 Star5 Star 11 rates

This page has no comments.