CODEWITHSUNDEEP
sqlgoogle-bigquery
Layla Comparin
Layla Comparin

Reputation: 247

How do I make that difference from months using standardSQL (BigQuery)

I have the following query:

#standardSQL
SELECT distinct  (grand_total/months) AS avg, ((grand_total/days)) AS 
avg_day
FROM 
(select count(searchint.id) as Total, (DATE_DIFF(DATE ({{DATE_END}}), 
DATE ({{DATE_START}}), DAY)+1) AS days, ((12 * YEAR(TIMESTAMP({{DATE_END}})) + 
MONTH(TIMESTAMP({{DATE_END}}))) - (12 * YEAR(TIMESTAMP({{DATE_START}})) 
+ MONTH(TIMESTAMP({{DATE_START}}))) +1) AS months,
(select count(searchint.id) as Total
from `dbsearch`
where  cast(replace(searchint.createdDate,'Z','')as DateTime) >= 
cast({{DATE_START}} as DateTime)
and  cast(replace(searchint.createdDate,'Z','')as DateTime) <= 
cast(DATE_ADD(cast({{DATE_END}} as date),  Interval 1 day ) as DateTime)) AS grand_total
    from `dbsearch`
where  cast(replace(searchint.createdDate,'Z','')as DateTime) >= 
cast({{DATE_START}} as DateTime)
and  cast(replace(searchint.createdDate,'Z','')as DateTime) <= 
cast(DATE_ADD(cast({{DATE_END}} as date),  Interval 1 day ) as DateTime)
group by date(cast(replace(searchint.createdDate,'Z','')as DateTime))
ORDER BY 2 DESC) AS groupby

However, when I try to run BigQuery it gives the following error:

Function not found: YEAR at [5:180]

I understand it's because I'm using standardSQL, but how do I make that difference from months using standardSQL?

Upvotes: 0

Views: 1046

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

To find difference in months between two dates you better to use DATE_DIFF() 

DATE_DIFF(DATE_END, DATE_START, MONTH)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

StandardSQL in BigQuery supports the ISO/ANSI-standard function for extracting date parts. This is extract():

You want:

extract(year from <datecol>)
extract(month from <datecol>)

This is explained in the documentation.

Upvotes: 2

Related Questions