All Products
Search

This Product

    MaxCompute:DATEDIFF

    Document Center

    MaxCompute:DATEDIFF

    Last Updated:Dec 10, 2025

    The DATEDIFF function calculates the time difference between an end date end_date and a start date start_date. The difference is measured in the time unit specified by date_part.

    Syntax

    BIGINT | INT DATEDIFF(STRING|DATE|DATETIME|TIMESTAMP|TIMESTAMP_NTZ <end_date>, STRING|DATE|DATETIME|TIMESTAMP|TIMESTAMP_NTZ <start_date>[, STRING <date_part>])

-- Standard example.
-- Returns 1.
SELECT DATEDIFF(DATE '2025-03-02', DATE '2025-03-01', 'dd');

    Parameters

    • end_date, start_date: Required. Specifies a date or timestamp. The supported data types are STRING, DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ.

      If the input is a STRING, the function's behavior depends on the specific string format and the MaxCompute data type edition.

      • If the input string is in the DATE format (yyyy-mm-dd), such as 2025-07-21, the date_part parameter is not supported for both the MaxCompute 1.0 and 2.0 data type editions.

      • If the input string is in the DATETIME format (yyyy-mm-dd hh:mi:ss), such as 2025-07-21 00:00:00:

        • In the MaxCompute 1.0 data type edition, the value is implicitly converted to the DATETIME type. The date_part parameter is supported.

        • In the MaxCompute 2.0 data type edition, implicit conversion and the date_part parameter are not supported.

    • date_part: Optional. A STRING that specifies the time unit for the return value. If you do not specify this parameter, the default unit is day.

      The following values are supported:

      Time unit

      Value

      Year

      year or yyyy

      Month

      month, mon, or mm

      Week

      • week: A week starts on Monday. This is equivalent to week(monday).

      • week(weekday): A week starts on the specified day of the week (weekday).

        Valid values: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.

      ISO week

      isoweek uses the week boundaries defined in ISO 8601. An ISO week starts on Monday.

      Day

      day or dd

      Hour

      hour or hh

      Minute

      mi

      Second

      ss

      Millisecond

      ff3

      Microsecond

      ff6

    Return value

    Returns a BIGINT or INT value. The following rules apply:

    • If date_part is specified, a BIGINT value is returned. If date_part is not specified, an INT value is returned.

    • If end_date or start_date is not a STRING, DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ value, an error is returned.

    • If end_date is earlier than start_date, a negative value is returned.

    • If end_date, start_date, or date_part is NULL, NULL is returned.

    • The function's calculation method differs depending on whether BigQuery compatibility mode is enabled, if the input parameters are not of the TIMESTAMP_NTZ type:

      • By default (when BigQuery compatibility mode is disabled), the function first truncates the values to the unit specified by date_part and then calculates the difference. For example, the difference between DATE'25-01-01' and DATE'24-12-31' is 1 year or 1 month.

      • When you run the SET odps.sql.bigquery.compatible=true; command to enable BigQuery compatibility mode:

        • If date_part is year, month, week, or isoweek, the function first truncates the values to the unit specified by date_part and then calculates the difference. For example, the difference between TIMESTAMP'25-01-01' and TIMESTAMP'24-12-31' is 1 year or 1 month.

        • If date_part is day, hour, minute, second, millisecond, or microsecond, the function first calculates the total difference and then converts the result to the unit specified by date_part. For example, the difference between TIMESTAMP'25-01-01 00:00:00' and TIMESTAMP'24-12-31 23:59:59' is 0 days or 0 hours.

    Examples

    Example 1: Basic examples

    • Calculate the difference in days. 

      -- Returns 1.
SELECT DATEDIFF(DATE '2025-03-02', DATE '2025-03-01', 'dd');

    • Calculate the difference in hours. 

      -- Returns 4.
SELECT DATEDIFF(CAST('2023-10-26 14:00:00' AS DATETIME), CAST('2023-10-26 10:00:00' AS DATETIME), 'hh');

    • Calculate the difference in microseconds where end_date is earlier than start_date.

      -- Returns -86400000000. 
SELECT DATEDIFF(DATE '2025-03-01', DATE '2025-03-02', 'ff6');

    • Calculate the difference in weeks, with a week starting on Friday.

      -- Returns 13. 
SELECT DATEDIFF(DATETIME '2025-07-01 00:00:00', DATETIME '2025-03-28 01:30:09', 'week(friday)');

    • Calculate the difference in ISO weeks.

      -- Returns 20.
SELECT DATEDIFF(TIMESTAMP_NTZ '2025-06-07 10:30:00.123', TIMESTAMP_NTZ '2025-01-15 00:30:45.123456', 'isoweek');

    Example 2: Before and after enabling BigQuery compatibility mode

    -- Returns 5. In default mode, values are truncated before the difference is calculated.
SELECT DATEDIFF(CAST('2024-11-01 10:05:05' AS TIMESTAMP), CAST('2024-11-01 10:00:10' AS TIMESTAMP), 'mi'  ) AS diff_in_minute1;

-- Returns 4. In BigQuery compatibility mode, the difference is calculated and then converted to the specified unit. 
SET odps.sql.bigquery.compatible=true;
SELECT DATEDIFF(CAST('2024-11-01 10:05:05' AS TIMESTAMP), CAST('2024-11-01 10:00:10' AS TIMESTAMP), 'mi'  ) AS diff_in_minute1;

    Example 3: The input parameter is a string

    If the input end_date and start_date are of the STRING type, the function's behavior depends on the specific string format and the MaxCompute data type edition.

    • If the input string is in the DATE format (yyyy-mm-dd), such as 2025-07-21, the date_part parameter is not supported for both the MaxCompute 1.0 and 2.0 data type editions.

      • In the MaxCompute 1.0 data type edition:

        -- Set the data type edition to V1.0.
SET odps.sql.type.system.odps2=false;
SET odps.sql.hive.compatible=false;

-- Incorrect example: The input parameters are of the STRING type in the yyyy-mm-dd format, and the date_part parameter is specified. NULL is returned.
SELECT DATEDIFF('2025-07-01', '2025-06-28','day');

-- Correct example: The input parameters are of the STRING type in the yyyy-mm-dd format, and the date_part parameter is not specified. The default date_part is day.
SELECT DATEDIFF('2025-07-01', '2025-06-28');
-- The following result is returned.
+------+
| _c0  |
+------+
| 3    |
+------+

      • In the MaxCompute 2.0 data type edition:

        -- Set the data type edition to V2.0.
SET odps.sql.type.system.odps2=true;

-- Incorrect example: The input parameters are of the STRING type in the yyyy-mm-dd format, and the date_part parameter is specified. An error is returned.
SELECT DATEDIFF('2025-07-01', '2025-06-28','day');

-- Correct example: The input parameters are of the STRING type in the yyyy-mm-dd format, and the date_part parameter is not specified. The default date_part is day.
SELECT DATEDIFF('2025-07-01', '2025-06-28');
-- The following result is returned.
+------+
| _c0  |
+------+
| 3    |
+------+

    • If the input string is in the DATETIME format (yyyy-mm-dd hh:mi:ss), such as 2025-07-21 00:00:00:

      • In the MaxCompute 1.0 data type edition, the value is implicitly converted to the DATETIME type for the calculation. The date_part parameter is supported:

        -- Set the data type edition to V1.0.
SET odps.sql.type.system.odps2=false;
SET odps.sql.hive.compatible=false;

-- Correct example: The input parameters are of the STRING type and conform to the DATETIME format. The date_part parameter is supported.
SELECT DATEDIFF('2025-07-01 00:21:00', '2025-03-28 01:30:09', 'hour');
-- The following result is returned.
+------------+
| _c0        |
+------------+
| 2279       |
+------------+

      • In the MaxCompute 2.0 data type edition, implicit conversion and the date_part parameter are not supported. By default, the difference is calculated and expressed in days:

        -- Set the data type edition to V2.0.
SET odps.sql.type.system.odps2=true;
SET odps.sql.hive.compatible=false;

-- Incorrect example: The input parameters are of the STRING type and conform to the DATETIME format, but the date_part parameter is specified. An error is returned.
SELECT DATEDIFF('2025-07-01 00:21:00', '2025-03-28 01:30:09', 'hour');

-- Correct example: The input parameters are of the STRING type, and the date_part parameter is not specified. The difference is calculated in days by default.
SELECT DATEDIFF('2025-07-01 00:21:00', '2025-03-28 01:30:09');
-- The following result is returned.
+------------+
| _c0        | 
+------------+
| 95         | 
+------------+

    Related functions

    DATEDIFF is a date function. For more information about functions for date calculations and conversions, see Date functions.