The topic describes how to use the DATEDIFF function. This function calculates the difference between two date values.

Notice This topic applies only to Ververica Platform (VVP) 2.4.0, Ververica Runtime (VVR) 3.0.0, and their later versions.

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
  • A date of the VARCHAR type is in the yyyy-MM-dd or yyyy-MM-dd HH:mm:ss format.
  • If an input parameter is NULL or a parsing error occurs, NULL is returned.

Example

  • Test data
    datetime1(VARCHAR) datetime2(VARCHAR) nullstr(VARCHAR)
    2017-10-15 00:00:00 2017-09-15 00:00:00 NULL
  • Test statement
    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 result
    int1(INT) int2(INT) int3(INT) int4(INT) int5(INT) int6(INT) int7(INT)
    30 30 -30 NULL NULL NULL 30