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

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

For more information, see DATEFORMAT Function.

Examples

Tip

For additional examples, see Common Tasks.

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:

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.

In this section: