Add one year to age based on date BigQuery

I have a table containing birthday information:

BirthYear             CurrentAgeResults     ExpectedAgeResults
2016-06-01T00:00:00   4.5                   3.5
2017-06-01T00:00:00   3.5                   2.5
2018-06-01T00:00:00   2.5                   1.5

I’m trying to calculate the current age but only want to add on a year when the date is 12-01. For example the first row was born in June, 2016. The current age should be 3.5 and come Dec 1, 2020 the age would increase to 4.5. So I only want it to add on a year in the winter time when the date is Dec 1. I have special survival calculations for wildlife species I need to make and it’s based on Dec1 – Nov30. The species I’m interested in is always born on June 1 (with the year varying). I always want it to show .5 never 0.0.

As my query currently stands, the current age is 4.5. Here’s what I have so far:

UPDATE `tableA`
SET CurrentAge = DATE_DIFF(CURRENT_DATE(), DATE(extract(year FROM BirthYear), 12, 1), YEAR ) + 0.5
WHERE CurrentAge is null ;

Answer

Per comments..

Trying to display in Age.0 and Age.5 increments? If so, perhaps calculate years first, then calculate months (case or other logic to set to 0 or 0.5), then concat the two values.

Advertisement

Answer