datediff()

Returns the difference between the start date and end date in the specified time unit.

Syntax

datediff(START_DATE, END_DATE, "{year|mon|day|hour|min|sec|msec}")
Required Parameters
START_DATE
Expression that returns the start date. The function returns null if a value of any other type is received.
END_DATE
Expression that returns the last date. The function returns null if a value of any other type is received.
"{year|mon|day|hour|min|sec|msec}"
Time unit to use when calculating the difference between START_DATE and END_DATE, enclosed in a pair of double quotes. For the meaning of each unit of time, refer to the table below.
Unit of TimeDescription
yearYear
monMonth
dayDay
hourHour
minMinute
secSecond
msecMillisecond

Usage

Calculate the difference between September 29, 2014, and September 29, 2013.

# Yearly
| datediff(date("2013-09-29", "yyyy-MM-dd"),
           date("2014-09-29", "yyyy-MM-dd"), "year") => 1

# Monthly
| datediff(date("2013-09-29", "yyyy-MM-dd"),
           date("2014-09-29", "yyyy-MM-dd"), "mon") => 12

# Daily
| datediff(date("2013-09-29", "yyyy-MM-dd"),
           date("2014-09-29", "yyyy-MM-dd"), "day") => 365

# Hourly
| datediff(date("2013-09-29", "yyyy-MM-dd"),
           date("2014-09-29", "yyyy-MM-dd"), "hour") => 8760

# In minutes
| datediff(date("2013-09-29", "yyyy-MM-dd"), 
           date("2014-09-29", "yyyy-MM-dd"), "min") => 525600

# In seconds
| datediff(date("2013-09-29", "yyyy-MM-dd"), 
           date("2014-09-29", "yyyy-MM-dd"), "sec") => 31536000

# In milliseconds
| datediff(date("2013-09-29", "yyyy-MM-dd"), 
           date("2014-09-29", "yyyy-MM-dd"), "msec") => 31536000000

# In the case of an incorrect input
datediff(null, date("2014-09-29", "yyyy-MM-dd"), "sec") => null

datediff(date("2013-09-29", "yyyy-MM-dd"), null, "min") => null

datediff("invalid", date("2014-09-29", "yyyy-MM-dd"), "min") => null