Date of Birth Compare Current date

[manoharnch]

Hi, 

im working on a bigquery to extract accurate  total days, months and years from date of birth to current date 
example if dateofbirth is July 29 1970 the and today is Sep 26th 2023 the result should be 53 years 1 months 28 days

please help on the query for this

[ms4446]

Below is a query that should help you calculate the years, months, and days from a date of birth to the current date:

#standardSQL
WITH data AS (
  SELECT date_of_birth, CURRENT_DATE() AS current_date
  FROM `your_dataset.your_table`
)
SELECT
  date_of_birth,
  current_date,
  DATE_DIFF(current_date, date_of_birth, YEAR) AS years,
  DATE_DIFF(current_date, DATE_ADD(date_of_birth, INTERVAL DATE_DIFF(current_date, date_of_birth, YEAR) YEAR), MONTH) AS months,
  DATE_DIFF(current_date, DATE_ADD(date_of_birth, INTERVAL DATE_DIFF(current_date, date_of_birth, YEAR) YEAR + DATE_DIFF(current_date, DATE_ADD(date_of_birth, INTERVAL DATE_DIFF(current_date, date_of_birth, YEAR) YEAR), MONTH) MONTH), DAY) AS days
FROM data;