On April 28, 2021, Google changed the required permissions for attaching IAM roles to service accounts. If you are using IAM roles for your Google service accounts, please see Changes to User Management.
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.
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(date(2015,02,15),'yyyy-MM-dd')
Output: Returns the formatted date value: 2015-02-15.
Column reference values:
dateformat(date(myYear, myMonth, myDay),'MMM yyyy')
Output: Returns date values based on three columns, formatted for date.
Syntax and Arguments
dateformat(date(year_integer_col,month_integer_col,day_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 |
| 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 |
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 |
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.
This page has no comments.