DateDiff gives different results between Quicksight and Athena

Has anyone run into a problem with Athena and Quicksight giving different “date difference” results?

DATE_DIFF(‘year’, DATE(date1), DATE(date2))

dateDiff({date1}, {date2}, ‘YYYY’)

date1 : 1986/12/13
date2 : 2021/10/18

Athena answer = 34
Quicksight answer = 35

the actual result is 34.8 years. Athena is truncating the fractional part whereas QuickSight is rounding it. You can always work with months and divide yourself to also get the fractional part and then decide whether to use round() or to truncate it using decimalToInt().

  round( dateDiff({date1}, {date2}, ‘MM’) / 12 )


  decimalToInt( dateDiff({date1}, {date2}, ‘MM’) / 12 )
1 Like