I am getting “Function not found: day at [5:84]”

SELECT   field_3103,
         Sum(
         CASE
                  WHEN field_3103 >=     Date(Extract(year FROM field_3103), Extract(month FROM field_3103), 1)
                  AND      field_3103 <= Date( Extract(year FROM field_3103), Extract(month FROM field_3103), Day(Last_day(field_3103))) THEN 1
                  ELSE 0
         END) AS new_listings,
         Sum(
         CASE
                  WHEN field_3102 >=     Date(Extract(year FROM field_3102), Extract(month FROM field_3102), 1)
                  AND      field_3102 <= Date(Extract(year FROM field_3102), Extract(month FROM field_3102), Day(Last_day(field_3102))) THEN 1
                  ELSE 0
         END) AS sold_listings
FROM     winter-agility-327715.properties_dataset.final_table
GROUP BY 1

This query results in the following error message:

“Function not found: day at [5:84]”

The syntax error in my SQL is also underlined as can be seen in this screen shot:

Answer

As mentioned by @Thorsten and @Samuel, you are getting this error as there is no DAY function in BigQuery. If you want to extract day you can use the following syntax:

EXTRACT(DAY FROM date_expression)

For more information on Date functions in BigQuery refer to this documentation.