All Products
Search

This Product

    MaxCompute:DATEDIFF

    Document Center

    MaxCompute:DATEDIFF

    Last Updated:Mar 25, 2026

    Returns the difference between end_date and start_date (end_date - start_date), expressed in the unit specified by date_part.

    By default, DATEDIFF counts the number of date_part boundaries crossed between the two timestamps — not the total elapsed time. For example, DATE '2025-01-01' and DATE '2024-12-31' differ by 1 year and 1 month, because both a year boundary and a month boundary are crossed. To measure elapsed time more precisely, use a finer-grained date_part such as 'day' or 'hour'.

    Syntax

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

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

    Parameters

    end_date, start_date

    Required. Supported types: STRING, DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ.

    The result is computed as end_date - start_date. A negative value is returned if end_date is earlier than start_date.

    When the input is a STRING, behavior depends on both the string format and the MaxCompute data type edition:

    • DATE format (yyyy-mm-dd, e.g., 2025-07-21): date_part is not supported in the 1.0 and 2.0 data type editions.

    • DATETIME format (yyyy-mm-dd hh:mi:ss, e.g., 2025-07-21 00:00:00):

    date_part

    Optional. A STRING specifying the time unit for the return value. Defaults to day when not specified.

    Time unitValue
    Yearyear or yyyy
    Monthmonth, mon, or mm
    Weekweek (starts Monday; equivalent to week(monday)) or week(weekday), where weekday is one of: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY
    ISO weekisoweek — uses ISO 8601 week boundaries; starts on Monday
    Dayday or dd
    Hourhour or hh
    Minutemi
    Secondss
    Millisecondff3
    Microsecondff6

    Return value

    Returns BIGINT if date_part is specified; returns INT if date_part is not specified.

    The following special cases apply:

    • Returns a negative value if end_date is earlier than start_date.

    • Returns NULL if end_date, start_date, or date_part is NULL.

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

    Calculation behavior and BigQuery compatibility mode

    The calculation method differs based on whether BigQuery compatibility mode is enabled. This only applies when the input is not of the TIMESTAMP_NTZ type.

    date_partDefault modeBigQuery compatibility mode (SET odps.sql.bigquery.compatible=true;)
    year, month, week, isoweekTruncates both values to the date_part unit, then counts the boundary differenceSame as default: truncates first, then counts the boundary difference
    day, hour, mi, ss, ff3, ff6Truncates both values to the date_part unit, then counts the boundary differenceCalculates the total elapsed difference, then converts to the date_part unit

    Example: The difference between TIMESTAMP '2025-01-01 00:00:00' and TIMESTAMP '2024-12-31 23:59:59':

    • Default mode: 0 days, 0 hours (after truncation to day/hour, both timestamps fall in the same unit)

    • BigQuery compatibility mode: 0 days, 0 hours (1 second total elapsed; less than 1 day or 1 hour)

    For year and month, both modes return the same result: DATE '2025-01-01' vs DATE '2024-12-31' returns 1 year and 1 month (one year boundary and one month boundary crossed).

    Examples

    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 when 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');

    Default mode vs. BigQuery compatibility mode

    The following example shows how the same query returns different results depending on whether BigQuery compatibility mode is enabled. The two TIMESTAMP values are 4 minutes and 55 seconds apart.

    -- Default mode: truncates both timestamps to the minute before calculating.
-- 10:05:05 truncates to 10:05; 10:00:10 truncates to 10:00. Difference = 5 minutes.
-- Returns 5.
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;

-- BigQuery compatibility mode: calculates total elapsed seconds, then converts to minutes.
-- 4 minutes 55 seconds = 295 seconds. Floor(295 / 60) = 4 minutes.
-- Returns 4.
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;

    STRING input

    When end_date and start_date are STRING values, behavior depends on the string format and the MaxCompute data type edition.

    DATE format strings (`yyyy-mm-dd`)

    date_part is not supported regardless of the data type edition. Specifying date_part returns NULL (1.0 edition) or an error (2.0 edition).

    In the MaxCompute 1.0 data type edition:

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

-- Incorrect: date_part is specified. NULL is returned.
SELECT DATEDIFF('2025-07-01', '2025-06-28', 'day');

-- Correct: date_part is omitted. Defaults to day.
SELECT DATEDIFF('2025-07-01', '2025-06-28');
-- Result:
+------+
| _c0  |
+------+
| 3    |
+------+

    In the MaxCompute 2.0 data type edition:

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

-- Incorrect: date_part is specified. An error is returned.
SELECT DATEDIFF('2025-07-01', '2025-06-28', 'day');

-- Correct: date_part is omitted. Defaults to day.
SELECT DATEDIFF('2025-07-01', '2025-06-28');
-- Result:
+------+
| _c0  |
+------+
| 3    |
+------+

    DATETIME format strings (`yyyy-mm-dd hh:mi:ss`)

    In the MaxCompute 1.0 data type edition, the string is implicitly converted to DATETIME and date_part is supported:

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

-- Correct: string in DATETIME format; date_part is supported.
SELECT DATEDIFF('2025-07-01 00:21:00', '2025-03-28 01:30:09', 'hour');
-- Result:
+------------+
| _c0        |
+------------+
| 2279       |
+------------+

    In the MaxCompute 2.0 data type edition, implicit conversion and date_part are not supported. The difference is calculated in days by default:

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

-- Incorrect: date_part is specified. An error is returned.
SELECT DATEDIFF('2025-07-01 00:21:00', '2025-03-28 01:30:09', 'hour');

-- Correct: date_part is omitted. Difference is calculated in days.
SELECT DATEDIFF('2025-07-01 00:21:00', '2025-03-28 01:30:09');
-- Result:
+------------+
| _c0        |
+------------+
| 95         |
+------------+

    Related functions

    DATEDIFF is a date function. For more information about date calculation and conversion functions, see Date functions.