NOTE: More on Date Increments (INTCK and INTNX)

It's always encouraging to get feedback about my blog articles and/or see an article spark some conversation. Last week's Date Increments (INTNX and INTCK) featured the INTNX function for incrementing (or decrementing) date, time, and datetime values by specific intervals such as HOUR and MONTH. I highlighted the optional fourth parameter that can be used to specify where in the interval the result should lie. The article created a small flurry of tweets from @DavidKBooth and @LaurieFleming, including:

@DavidKBooth:

@LaurieFleming @aratcliffeuk just discovered intck('year', birthday, date, 'C') which correctly calculates age in years!

@DavidKBooth:

@LaurieFleming @aratcliffeuk it assumes people born on 29feb celebrate birthdays on 28feb in non leap years.

@LaurieFleming:

@DavidKBooth @aratcliffeuk Excellent. That's much so better than floor((intck('month', &birth, &date) - (day(&date) < day(&birth))) / 12)

@DavidKBooth:

@LaurieFleming @aratcliffeuk 4th parameter added in 9.2 - would have been cross if I'd been overlooking it for ages.

 

To be perfectly honest, I've used INTNX a tremendous amount, but strangely I've never used INTCK half as much. I hadn't even realised that INTCK's fourth parameter took different values to INTNX. The valid values are DISCRETE (default) and CONTINUOUS. DISCRETE counts the number of interval boundaries between the two dates; CONTINUOUS counts the number of intervals, starting from the start date, thus it is well suited to calculating (as Dave says) ages. So, today was another learning day for me!