CODEWITHSUNDEEP
sqldategoogle-bigquerycomparison
Harvey
Harvey

Reputation: 209

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 ?

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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'))

Upvotes: 1

Related Questions