Correct Syntax for AEP Query - Counts

[montezh2001]

Hello, 

I'm trying to get a count of all the profiles on my dataset where the DOB is less than 21 years old from the Current Date. I'm trying to use the following syntax in the Query service but get an error: 

SELECT
person.birthDate AS birthDate,
COUNT(1) eventCount
FROM
dataset_aep_coreprofile
WHERE
DATE_ADD (YEAR, 21, person.birthDate) > CURRENT_DATE;

GROUP BY
person.birthDate

ErrorCode: 42703 queryId: 9be3e30e-29df-49a9-b78e-06eb65e0cf73 Column YEAR does not exist.

I'm having a hard time understanding what SQL this service is using. Example: I'm used to using GETDATE() but I have to use CURRENT_DATE in this query. 

Please advise. 

[DavidRoss91 · Accepted solution]

@montezh2001 try this:

SELECT

person.birthDate AS birthDate,

COUNT(1) AS eventCount

FROM

dataset_aep_coreprofile

WHERE

person.birthDate + INTERVAL '21 years' > CURRENT_DATE

GROUP BY

person.birthDate;

[MontexHo]

@DavidRoss91 - that code was 99% accurate. Here's the final code that worked: 

SELECT
person.birthDate AS birthDate,
COUNT(1) AS eventCount
FROM
dataset_aep_coreprofile
WHERE
person.birthDate + INTERVAL '21 YEAR' > CURRENT_DATE
GROUP BY
person.birthDate;

The code didn't like 'years' syntax. YEAR is the syntax to use. Thank you still, this lead me to the right coding to use. 

[DavidRoss91]

@MontexHo No problem at all, happy to help. This doc could be handy in the future for you:

SQL Syntax in Query Service | Adobe Experience Platform