calculate the age from a column in a bigquery table

Question

i would like to calculate the age of users from a column (YOB) which contain only the year of birthday ? i've tried that but it doesnt work :

WITH X AS (SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS YEAR) SELECT X - YOB from dataset.table;

Could you help please ?

Thanks!

Answer 1

Try DATE_DIFF.

If YOB is an integer:

WITH test_data AS (
  SELECT 1999 AS YOB UNION ALL
  SELECT 2005 UNION ALL
  SELECT 2019
)
SELECT YOB, DATE_DIFF(CURRENT_DATE(), DATE(YOB, 1, 1), YEAR) AS age
FROM test_data

If YOB is a string:

WITH test_data AS (
  SELECT '1999' AS YOB UNION ALL
  SELECT '2005' UNION ALL
  SELECT '2019'
)
SELECT YOB, DATE_DIFF(CURRENT_DATE(), PARSE_DATE("%Y", YOB), YEAR) AS age
FROM test_data