Contents:
- 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:
derive type:single value: DATEFORMAT(DATETIME(2015,02,15, 2, 4, 0),'yyyy-MM-dd:HH:mm:ss')
Output: Generates a column of values containing the formatted date value: 2015-02-15:02:04:00.
Column reference values:
derive type:single value: DATEFORMAT(DATETIME(myYear, myMonth, myDay, myHour, myMin, mySec),'MMM yyyy - HH:MM:SS')
Output: Generates a column of date values where:
MMM= short value formyMonthyyyy= value frommyYearHH= value frommyHourMM= value frommyMinSS= value frommySec
Syntax and Arguments
derive type:single value: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
1or more, with a maximum value of12. - 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
1or more, with a maximum value for any month of31. - 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
0or more, with a maximum value for any hour of23. - 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
0or more, with a maximum value for any minute of59. - 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
0or more, with a maximum value for any second of59. - 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.
Tip: For additional examples, see Common Tasks.
Examples
Example - date and time functions
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 |
Transform:
derive type:single value: DATEFORMAT(DATE (year, month, day),'yyyy-MM-dd') as:'fctn_date'
derive type:single value: DATEFORMAT(TIME (hour, minute, second),'HH-mm-ss') as:'fctn_time'
derive type:single value: DATEFORMAT(DATETIME (year, month, day, hour, minute, second),'yyyy-MM-dd-HH:mm:ss') as:'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.
This page has no comments.