Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Can't EXTRACT year of the date with BigQuery | error:No matching signature for function EXTRACT

I’m trying to extract the year of a date in order to calculate the age of user.
The ‘birthday’ is a string format in YYYY-MM-DD.

Here is my code

SELECT username, birthday, CURRENT_DATE(),
    EXTRACT(YEAR FROM CURRENT_DATE()) - EXTRACT(YEAR FROM birthday) as age ,    
    FROM 
        `user`;

My SQL table ‘user’:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

username birthday
Name1 1999-05-11
Name2 1999-05-11

But the "EXTRACT(YEAR FROM birthday)" don’t work and I don’t find where is the problem.

The complete error :

No matching signature for function EXTRACT for argument types: DATE_TIME_PART FROM STRING. Supported signatures: EXTRACT(DATE_TIME_PART FROM DATE); EXTRACT(DATE_TIME_PART FROM TIMESTAMP [AT TIME ZONE STRING]); EXTRACT(DATE_TIME_PART FROM DATETIME); EXTRACT(DATE_TIME_PART FROM TIME); EXTRACT(DATE_TIME_PART FROM INTERVAL) at [2:41]

>Solution :

Use below

select username, birthday, current_date(),
extract(year from current_date()) - extract(year from date(birthday)) as age ,    
from `user`;        

Note date(birthday) to fix a "problem"

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading