---
url: /v6.2/en-us/calculation-function-date.md
---
# Date Functions

## add_day

| Syntax       | add_day(T,I)              |
|--------------|---------------------------|
| Parameters   | 1. Time type parameter;2. Number of days to add, optional type: number |
| Return Type  | Time                      |
| Description  | Returns the date after the specified number of days from the starting date. If the numeric field is negative, it returns the date before the specified number of days. |
| Example      | add_day('2018-02-02', 3), returns: 2018-02-05 |

## add_hour

| Syntax       | add_hour(T,I)             |
|--------------|---------------------------|
| Parameters   | 1. Time type parameter;2. Number of hours to add, optional type: numeric |
| Return Type  | Time                      |
| Description  | Returns the time after adding the specified number of hours to the starting time. If the numeric field is negative, it returns the time before the specified number of hours. |
| Example      | add_hour('2018-02-02 08:30:00.000', 3), returns: 2018-02-02 11:30:00.000 |

## add_minute

| Syntax       | add_minute(T,I)           |
|--------------|---------------------------|
| Parameters   | 1. Time type parameter;2. Minutes to add, optional type: number |
| Return Type  | Time                      |
| Description  | Returns the time after adding the specified number of minutes to the starting time. If the numeric field is negative, it returns the time before the specified number of minutes. |
| Example      | add_minute('2018-02-02 08:30:00.000', 3), returns: 2018-02-02 08:33:00.000 |

## add_month

| Syntax       | add_month(T,I)            |
|--------------|---------------------------|
| Parameters   | 1. Time type parameter;2. Number of months to add, optional type: number |
| Return Type  | Time                      |
| Description  | Returns the date after the specified number of months from the starting date. If the numeric field is negative, it returns the date before the specified number of months. |
| Example      | add_month('2018-02-02', 3), returns: 2018-05-02 |

## add_quarter

| Syntax       | add_quarter(T,I)          |
|--------------|---------------------------|
| Parameters   | 1. Time type parameter;2. Number of quarters to add, optional type: number |
| Return Type  | Time                      |
| Description  | Returns the date after the specified number of quarters from the start date. If the numeric field is negative, it returns the date before the specified number of quarters. |
| Example      | add_quarter('2018-02-02', 3), returns: 2018-05-02 |

## add_week

| Syntax       | add_week(T,I)             |
|--------------|---------------------------|
| Parameters   | 1. Time type parameter;2. Number of weeks to add, optional type: number |
| Return Type  | Time                      |
| Description  | Returns the date after the specified number of weeks from the start date. If the numeric field is negative, it returns the date before the specified number of weeks. |
| Example      | add_week('2018-02-02', 3), returns: 2018-05-02 |

## add_year

| Syntax       | add_year(T,I)             |
|--------------|---------------------------|
| Parameters   | 1. Time type parameter;2. Number of years to add, optional type: number |
| Return Type  | Time                      |
| Description  | Returns the date after the specified number of years from the starting date. If the numeric field is negative, it returns the date before the specified number of years. |
| Example      | add_year('2018-02-02', 3), returns: 2021-02-02 |

## add_year_keep_woy

| Syntax         | add_year_keep_woy(T,I)    |
|----------------|---------------------------|
| Parameter Description | 1. Time type parameter;2. Number of years to add, optional type: number |
| Return Type   | Time                  |
| Description   | Similar to add_year, but adjusts the date to keep the week of the year unchanged |
| Example       | add_year_keep_woy('2023-01-01', 1), returns: 2024-01-01 (example, specific result may vary depending on week calculation logic) |
| Unsupported Data Sources | mysql, tidb, sqlserver, redshift, spark, hive, impala, presto, db2, mongodb, maxcompute |

## age_in_days

| Syntax       | age_in_days(START, END)   |
|--------------|---------------------------|
| Parameters   | 1. Start time, time-type parameter;2. End time, time-type parameter |
| Return Type  | Number                    |
| Description  | Compares the difference in days between two times, with all time components involved in the calculation. |
| Example      | age_in_days('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 364 |

## age_in_hours

| Syntax        | age_in_hours(START, END)  |
|---------------|---------------------------|
| Parameters    | 1. Start time, time type parameter;2. End time, time type parameter |
| Return Type   | Number                    |
| Description   | Compares the difference between two times in hours, with all time components included in the calculation. |
| Example       | age_in_hours('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 8759 |

## age_in_milliseconds

| Syntax         | age_in_milliseconds(START, END) |
|----------------|----------------------------------|
| Parameter Description | 1. Start time, time type parameter;2. End time, time type parameter |
| Return Type   | Number                           |
| Description   | Compares the difference in milliseconds between two times, with all time components included in the calculation. |
| Example       | age_in_milliseconds('2021-05-31 00:00:00.531', '2022-05-31 00:00:00.000'), returns: 31535999469 |

## age_in_minutes

| Syntax         | age_in_minutes(START, END) |
|----------------|----------------------------|
| Parameter Description | 1. Start time, time-type parameter;2. End time, time-type parameter |
| Return Type   | Number                     |
| Description   | Compares the difference between two times in minutes, with all time components involved in the calculation. |
| Example       | age_in_minutes('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 525599 |

## age_in_months

| Syntax         | age_in_months(START, END) |
|----------------|---------------------------|
| Parameter Description | 1. Start time, time type parameter;2. End time, time type parameter |
| Return Type   | Number                    |
| Description   | Compares the difference in months between two times, with all time components participating in the calculation. |
| Example       | age_in_months('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 11 |

## age_in_seconds

| Syntax         | age_in_seconds(START, END) |
|----------------|----------------------------|
| Parameter Description | 1. Start time, time type parameter;2. End time, time type parameter |
| Return Type   | Number                     |
| Description   | Compares the difference between two times in seconds, with all time components included in the calculation. |
| Example       | age_in_seconds('2021-05-31 00:00:00.531', '2022-05-31 00:00:00.000'), returns: 31535999 |

## age_in_years

| Syntax         | age_in_years(START, END)  |
|----------------|---------------------------|
| Parameter Description | 1. Start time, time-type parameter;2. End time, time-type parameter |
| Return Type   | Number                    |
| Description   | Compares the difference in years between two times, with all time components participating in the calculation. |
| Example       | age_in_years('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 0 |

## diff_in_day

| Syntax         | diff_in_day(START,END)    |
|----------------|---------------------------|
| Parameter Description | 1. Specify the start, optional type: time;2. Specify the end, optional type: time |
| Return Value Type   | Number                  |
| Description         | Compares the difference in days between two dates. |
| Example         | diff_in_day('2015-02-03', '2018-02-03'), returns: 1095 |

## diff_in_hour

| Syntax         | diff_in_hour(START,END)   |
|----------------|---------------------------|
| Parameter Description | 1. Specify the start time, optional type: time;2. Specify the end time, optional type: time |
| Return Type   | Number                  |
| Description   | Compares the difference between two times in hours. |
| Example       | diff_in_hour('2015-03-24 16:32:32', '2018-02-02 12:34:43'), returns: 25100 |

## diff_in_minute

| Syntax         | diff_in_minute(START,END) |
|----------------|---------------------------|
| Parameter Description | 1. Specify the start time, optional type: time;2. Specify the end time, optional type: time |
| Return Type   | Number                  |
| Description   | Compares the difference between two times in minutes. |
| Example       | diff_in_minute('2015-03-24 16:32:32', '2018-02-02 12:34:43'), returns: 1506002 |

## diff_in_month

| Syntax         | diff_in_month(START,END)  |
|----------------|---------------------------|
| Parameter Description | 1. Specify the start, optional type: time;2. Specify the end, optional type: time |
| Return Type   | Number                  |
| Description   | Compares the difference in months between two dates. |
| Example       | diff_in_month('2015-02-03', '2018-02-03'), returns: 36 |

## diff_in_year

| Syntax         | diff_in_year(START,END)   |
|----------------|---------------------------|
| Parameter Description | 1. Specify the start, optional type: time;2. Specify the end, optional type: time |
| Return Type   | Number                  |
| Description   | Compares the difference in years between two times. |
| Example       | diff_in_year('2015-02-03', '2018-02-03'), returns: 3 |

## end_of_month

| Syntax       | end_of_month(T)           |
|--------------|---------------------------|
| Parameter    | Time type parameter       |
| Return Type  | Time                      |
| Description  | Retrieves the start time of the last day of the month for the given time field |
| Example      | end_of_month('2018-02-02'), returns: 2018-02-28 |

## extract_day

| Syntax       | extract_day(T)            |
|--------------|---------------------------|
| Parameter    | Time type parameter       |
| Return Type  | Number                    |
| Description  | Retrieves the day part of the parameter, indicating which day of the month it is. |
| Example      | extract_day('2018-02-02'), returns: 2 |

## extract_dow

| Syntax       | extract_dow(T)            |
|--------------|---------------------------|
| Parameter Description | Time-type parameter |
| Return Value Type | Number                  |
| Description   | Retrieves the day part of the parameter, indicating which day of the week it is. |
| Example       | extract_dow('2018-02-02'), returns: 5 |

## extract_doy

| Syntax        | extract_doy(T)            |
|---------------|---------------------------|
| Parameter     | Time type parameter       |
| Return Type   | Number                    |
| Description   | Retrieves the day part of the parameter, indicating which day of the year it is. |
| Example       | extract_doy('2018-02-02'), returns: 33 |

## extract_hour

| Syntax       | extract_hour(T)           |
|--------------|---------------------------|
| Parameter Description | Time-type parameter |
| Return Value Type | Number                  |
| Description   | Retrieves the hour part from the parameter, representing the current hour. |
| Example       | extract_hour('2018-02-03 13:23:21'), returns: 13 |

## extract_minute

| Syntax       | extract_minute(T)         |
|--------------|---------------------------|
| Parameter    | Time type parameter       |
| Return Type  | Number                    |
| Description  | Retrieves the minute part of the parameter, representing the current minute. |
| Example      | extract_minute('2018-02-03 13:23:21'), returns: 23 |

## extract_month

| Syntax        | extract_month(T)          |
|---------------|---------------------------|
| Parameter     | Time type parameter       |
| Return Type   | Number                    |
| Description   | Retrieves the month part from the parameter. |
| Example       | extract_month('2018-02-02'), returns: 2 |

## extract_quarter

| Syntax        | extract_quarter(T)        |
|---------------|---------------------------|
| Parameter     | Time type parameter       |
| Return Type   | Number                    |
| Description   | Retrieves the quarter part from the parameter. |
| Example       | extract_quarter('2018-02-02'), returns: 1 |

## extract_second

| Syntax       | extract_second(T)         |
|--------------|---------------------------|
| Parameter    | Time type parameter       |
| Return Type  | Number                    |
| Description  | Retrieves the second part of the parameter, representing the current second. |
| Example      | extract_second('2018-02-03 13:23:21'), returns: 21 |

## extract_week

| Syntax        | extract_week(T)           |
|---------------|---------------------------|
| Parameter     | Time type parameter       |
| Return Type   | Number                    |
| Description   | Retrieves the week part of the parameter, indicating which week of the year the given day belongs to. |
| Example       | extract_week('2018-02-02'), returns: 4 |

## extract_year

| Syntax       | extract_year(T)          |
|--------------|---------------------------|
| Parameter    | Time type parameter       |
| Return Type  | Number                    |
| Description  | Retrieves the year part from the parameter. |
| Example      | extract_year('2018-02-02'), returns: 2018 |

## month_weekday_count

| Syntax         | month_weekday_count(YEAR,MONTH) |
|----------------|---------------------------------|
| Parameter Description | 1. Year, optional type: number; 2. Month, optional type: number |
| Return Value Type   | Number                  |
| Description         | Retrieves the number of weekdays in the specified month. |
| Example         | month_weekday_count(2018, 2), returns: 20 |
| Unsupported Data Sources | mysql, tidb, sqlserver, redshift, oracle, impala, hive, spark, db2, mongodb, maxcompute |

## now

| Syntax        | now()                     |
|---------------|---------------------------|
| Parameters    | None                      |
| Return Type   | Time                      |
| Description   | Returns the current time, including hour, minute, and second information. |
| Example       | now(), returns: current system time (e.g., 2024-05-20 15:30:45) |

## parse_time

| Syntax         | parse_time(S,LITERAL_PATTERN) |
|----------------|---------------------------|
| Parameter Description | 1. Text type parameter;2. Specified pattern, optional type: string |
| Return Value Type   | Time                  |
| Description         | Parses the `s` parameter into a time type using `literal_pattern` |
| Example         | parse_time('2024-05-20', 'yyyy-MM-dd'), returns: 2024-05-20 |
| Unsupported Data Source | sqlserver |

## time_in_range

| Syntax       | time_in_range(T,S)        |
|--------------|---------------------------|
| Parameter Description | 1. Time type parameter;2. Text type parameter |
| Return Type  | Boolean                  |
| Description  | Determines whether the date is within the specified range. Available ranges include: 'Current Year', 'Current Year-to-Date', 'Current Quarter', 'Current Quarter-to-Date', 'Current Month', 'Current Month-to-Date', 'Current Week', 'Current Week-to-Date', 'Today', 'Current Hour', 'Yesterday', 'Recent 7 days', 'Recent 30 days', 'Recent 90 days', 'All Available Date', 'All Invalid Date', 'All' |
| Example      | time_in_range('2024-05-20', 'Today'), returns: TRUE (if the current date is 2024-05-20) |

## today

| Syntax         | today()                   |
|----------------|---------------------------|
| Parameter      | None                      |
| Return Type    | Date                      |
| Description    | Returns the current date, excluding hour, minute, and second information. |
| Example        | today(), returns: the current system date (e.g., 2024-05-20) |

## trunc_day

| Syntax       | trunc_day(T)              |
|--------------|---------------------------|
| Parameter    | Time type parameter       |
| Return Type  | Time                      |
| Description  | Truncates the time or date column to the day. |
| Example      | trunc_day('2018-02-02 12:30:59'), returns: '2018-02-02' |

## trunc_hour

| Syntax       | trunc_hour(T)             |
|--------------|---------------------------|
| Parameter    | Time type parameter       |
| Return Type  | Time                      |
| Description  | Truncates the time column to the hour, setting finer granularity to 0. |
| Example      | trunc_hour('2018-02-02 12:23:32'), returns: '2018-02-02 12:00:00' |

## trunc_minute

| Syntax       | trunc_minute(T)           |
|--------------|---------------------------|
| Parameter Description | Time type parameter |
| Return Value Type | Time                  |
| Description   | Truncates the time column to minutes, setting all finer granularity to 0. |
| Example       | trunc_minute('2018-02-02 12:23:32'), returns: '2018-02-02 12:23:00' |

## trunc_month

| Syntax       | trunc_month(T)            |
|--------------|---------------------------|
| Parameter Description | Time type parameter |
| Return Value Type | Time                  |
| Description   | Truncates the time or date column to the month, setting the date to the 1st day of the month. |
| Example       | trunc_month('2018-02-02 12:23:32'), returns: '2018-02-01' |

## trunc_millisecond

| Syntax       | trunc_millisecond(T)      |
|--------------|---------------------------|
| Parameter    | Time type parameter       |
| Return Type  | Time                      |
| Description  | Truncates the time column to milliseconds, setting all finer granularity to 0. |
| Example      | trunc_millisecond('2018-02-02 12:23:32.123'), returns: '2018-02-02 12:23:32.123' |

## trunc_quarter

| Syntax       | trunc_quarter(T)          |
|--------------|---------------------------|
| Parameter Description | Time type parameter |
| Return Value Type | Time                  |
| Description   | Truncates the time or date column to the quarter, sets the month to the first month of the quarter, and the date to the 1st. |
| Example       | trunc_quarter('2018-05-02 12:23:32'), returns: '2018-04-01' |

## trunc_second

| Syntax       | trunc_second(T)           |
|--------------|---------------------------|
| Parameter Description | Time type parameter |
| Return Value Type   | Time                  |
| Description         | Truncates the time column to seconds, setting all finer granularity to 0. |
| Example         | trunc_second('2018-02-02 12:23:32.123'), returns: '2018-02-02 12:23:32.000' |

## trunc_week

| Syntax       | trunc_week(T)             |
|--------------|---------------------------|
| Parameter    | Time type parameter       |
| Return Type  | Time                      |
| Description  | Truncates the time or date column to the week, setting the month and date to the first day of the week. |
| Example      | trunc_week('2018-02-02'), returns: '2018-01-29' |

## trunc_year

| Syntax       | trunc_year(T)             |
|--------------|---------------------------|
| Parameter Description | Time type parameter |
| Return Value Type   | Time                  |
| Description         | Truncates the time or date column to the year, setting the month and day to January 1st. |
| Example         | trunc_year('2018-02-02 12:23:32'), returns: '2018-01-01' |