如何在BigQuery SQL中根据已过天数计算对应年月

The optimal way to solve this problem leverages BigQuery's built-in date functions, which automatically handle varying month lengths, leap years, and other edge cases without manual calculations.

Step-by-Step Approach #

1. Subtract the passed days from today's date: Use DATE_SUB() to adjust date_today by subtracting day_passed days. This function natively accounts for all date nuances (like 28/29-day Februaries, 30/31-day months).
2. Format the resulting date to year-month string: Use FORMAT_DATE() to convert the adjusted date into the YYYY-MM format you need.

Example SQL Query #

SELECT
  date_today,
  day_passed,
  FORMAT_DATE('%Y-%m', DATE_SUB(date_today, INTERVAL day_passed DAY)) AS month_joined
FROM
  your_table_name

Testing the Example #

For your sample input:

• date_today = '2018-01-22'
• day_passed = 32

DATE_SUB('2018-01-22', INTERVAL 32 DAY) returns 2017-12-21, and FORMAT_DATE('%Y-%m', ...) converts that to '2017-12'—exactly the desired result.

Handling Edge Cases #

This solution automatically handles tricky scenarios:

• Leap years: If date_today is '2020-03-01' and day_passed = 31, DATE_SUB correctly returns '2020-01-30', which formats to '2020-01'.
• Short months: Subtracting days from a 30-day month (e.g., April) into a 31-day month is handled seamlessly.
• Year transitions: Like your example, moving from January to December of the previous year works without extra logic.

Why this is optimal: It uses BigQuery's optimized, native date functions instead of manual case statements or custom calculations, which are error-prone and less efficient.

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