Search code examples
dategoogle-bigquery

Logic to check whether a two DATE FIELDS are exactly has one month gap (same DATE) i.e. 28 Feb & 28 Mar

Context:

I'd like to check whether the date from field B is ONE MONTH ahead of the date from field A. However, the definition of ONE MONTH is: field B date has exactly the same date with field A date.

Table Example:

date_a date_b flag
2024-01-05 2024-02-04 FALSE
2024-01-05 2024-02-05 TRUE

My Failed Trial:

...

CASE WHEN DATE_DIFF(date_b, date_a, MONTH) = 1 THEN TRUE ELSE FALSE END AS flag

...

However, that logic only takes into account the MONTH, without considering the date itself.

Any suggestion on this? Thanks!

Solution

  • An option could be use EXTRACT function to get the day from date and compare it:

    CASE 
  WHEN 
    DATE_DIFF(date_b, date_a, MONTH) = 1 
    AND EXTRACT(DAY FROM date_a) = EXTRACT(DAY FROM date_b)
  THEN TRUE 
  ELSE FALSE
END AS flag

    Another approach using DATE_ADD:

    (DATE_ADD(date_b, INTERVAL 1 MONTH) = date_a) AS flag