关于SQL查询报错'Function not found: day at [5:84]'的技术求助

Why This Error Happens #

The Function not found: day error pops up because you’re using Day() — a function that doesn’t exist in BigQuery. BigQuery adheres to standard SQL syntax, and while you could use EXTRACT(DAY FROM <date_value>) to pull the day component from a date, there’s an even better fix here: you don’t need to extract the day at all.

Your original query wastes effort reconstructing the first and last days of the month manually, but BigQuery has built-in functions that handle this directly:

• DATE_TRUNC(<date_field>, MONTH) returns the first day of the month for the given date.
• LAST_DAY(<date_field>) returns the last day of the month for the given date.

How to Fix It #

Simplify your query drastically with these built-in functions — this eliminates the invalid Day() call entirely and makes your code cleaner. Here’s the corrected SQL:

SELECT 
  field_3103,
  SUM(CASE 
        WHEN field_3103 >= DATE_TRUNC(field_3103, MONTH) 
             AND field_3103 <= LAST_DAY(field_3103) 
        THEN 1 
        ELSE 0 
      END) AS new_listings,
  SUM(CASE 
        WHEN field_3102 >= DATE_TRUNC(field_3102, MONTH) 
             AND field_3102 <= LAST_DAY(field_3102) 
        THEN 1 
        ELSE 0 
      END) AS sold_listings
FROM winter-agility-327715.properties_dataset.final_table
GROUP BY 1

Alternative Fix (If You Prefer the Original Structure) #

If you want to keep reconstructing dates (though this is less efficient and harder to read), replace Day(Last_day(field_3103)) with BigQuery’s valid syntax for extracting the day: EXTRACT(DAY FROM LAST_DAY(field_3103)). Here’s that version:

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), EXTRACT(DAY FROM 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), EXTRACT(DAY FROM LAST_DAY(field_3102))) 
        THEN 1 
        ELSE 0 
      END) AS sold_listings
FROM winter-agility-327715.properties_dataset.final_table
GROUP BY 1

The first fix is strongly recommended — it’s more maintainable and leverages BigQuery’s optimized date functions for better performance.

内容的提问来源于stack exchange，提问作者mac