This topic describes how to use the date function DATEDIFF in Realtime Compute.

Note We recommend that you use this function in Realtime Compute V3.3.0 and later. If your Realtime Compute is earlier than V3.3.0, the return value of this function may be inaccurate.

Syntax

INT DATEDIFF(VARCHAR enddate, VARCHAR startdate)
INT DATEDIFF(TIMESTAMP enddate, VARCHAR startdate)
INT DATEDIFF(VARCHAR enddate, TIMESTAMP startdate)
INT DATEDIFF(TIMESTAMP enddate, TIMESTAMP startdate)

Input parameters

Parameter Data type
startdate TIMESTAMP or VARCHAR
enddate TIMESTAMP or VARCHAR
Note The format of a VARCHAR type date is yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.

Description

Calculates the number of days between the end date and start date. The return value is an integer. If the input parameter is null or a parsing error occurs, the return value is null.

Example

  • Test data
    datetime1 (VARCHAR) datetime2 (VARCHAR) nullstr (VARCHAR)
    2017-10-15 00:00:00 2017-09-15 00:00:00 null
  • Test statements
    SELECT  DATEDIFF(datetime1, datetime2) as int1, 
            DATEDIFF(TIMESTAMP '2017-10-15 23:00:00',datetime2) as int2, 
            DATEDIFF(datetime2,TIMESTAMP '2017-10-15 23:00:00') as int3, 
            DATEDIFF(datetime2,nullstr) as int4, 
            DATEDIFF(nullstr,TIMESTAMP '2017-10-15 23:00:00') as int5, 
            DATEDIFF(nullstr,datetime2) as int6, 
            DATEDIFF(TIMESTAMP '2017-10-15 23:00:00',TIMESTAMP '2017-9-15 00:00:00')as int7
    FROM T1;
  • Test results
    int1 (INT) int2 (INT) int3 (INT) int4 (INT) int5 (INT) int6 (INT) int7 (INT)
    30 31 -31 null null null 31