DATEDIF - Date Difference

Text/start_date

start_date represents the starting date of the specified period.

end_date represents the ending date of the specified period.

Text/end_date

Unit

Represents the type of information returned by the function.

• If the unit is Y or y, the year difference is returned.

• If the unit is M or m, the month difference is returned.

• If the unit is W or w, the week difference is returned.

• If the unit is D or d, the day difference is returned.

• If the unit is MD or md, the day difference (ignoring the year and month) is returned.

• If the unit is YM or ym, the month difference (ignoring the year and day) is returned.

• If the unit is YD or yd, the day difference (ignoring the year) is returned.

• If the unit is H or h, the hour difference (ignoring the year) is returned.

• If the unit is MI or mi, the minute difference (ignoring the year) is returned.

• If the unit is S or s, the second difference (ignoring the year) is returned.

The unit is required to be a constant. Field references are not supported, but direct parameter input is allowed.

Note:

1. FineDataLink of V4.0.30 and later versions support the calculation of the difference in weeks/hours/minutes/seconds between two specific dates using the DATEDIF() function. If the difference is less than one week/hour/minute/second, the result is 0.

2. If you want to calculate the number of months between two specified dates and avoid discarding the part when it's less than one month, you can use the months_between() function of Spark SQL.

DATEDIF("2001/2/28","2004/3/20","Y")

3, indicating that there are three full years between February 28, 2001 and March 20, 2004.

DATEDIF("2001/2/28","2004/3/20","M")

37, indicating that there are 36 full months between February 28, 2001 and March 20, 2004.

DATEDIF("2001/2/28","2004/3/20","D")

1116, indicating that there are 1116 full days between February 28, 2001 and March 20, 2004.

DATEDIF("2001/2/28","2004/3/20","md")

8, indicating that there are 8 full days (ignoring the month and year) between February 28, 2001 and March 20, 2004.

DATEDIF("2001/1/28","2004/3/20","YM")

2, indicating that there are 2 full months (ignoring the month and day) between January 28, 2001 and March 20, 2004.

DATEDIF("2001/2/28","2004/3/20","yd")

21, indicating that there are 21 full days (ignoring the year) between February 28, 2001 and March 20, 2004.