Search code examples
sqldategoogle-bigquerycomparison

How to compare DD/MM dates in BigQuery?

I'm trying to compare two dates without the year, only the day and the month (DD/MM) when for example 30/04 < 02/05.

I want to be able to say wether a date is previous or not to another date based only on the day and the month without paying attention to the year.

I tried to use FORMAT_DATE("%d/%m", DATE("2021-04-30")) <= FORMAT_DATE("%d/%m", DATE("2021-05-02")) but unfortunatly it returned FALSE.

I cannot find something that could resolve this properly.

What would you do ?

Solution

  • If you want to compare values as ordered strings, then the ordering needs to make sense.

    Put the month first:

    FORMAT_DATE('%m/%d', DATE('2021-04-30')) <= FORMAT_DATE('%m/%d', DATE('2021-05-02'))