Bigquery replace value in a column based on previous year date condition

I need help to write a SQL logic in bigquery to replace a value in one column based on a condition as below

FINANCIAL YEAR –> JUL to JUN

How can I make it more dynamic so it rolls over next year

Condition --> If version = "ACT" an  DATE is not current year i.e 07-2020 to 06-2021 then replace ACT with ACTA

Current:

Version	DATE
ACT	202104
ACT	202101
ACT	202006
ACT	202001
ACT	202007
ACT	201903
ACT	202008
TAR	202104
TAR	202101
TAR	202006
TAR	202001
TAR	202007
TAR	201903
TAR	202008

Output required:

Version	DATE
ACT	202104
ACT	202101
ACT	202006
ACTA	202001
ACT	202007
ACTA	201903
ACT	202008
TAR	202104
TAR	202101
TAR	202006
TAR	202001
TAR	202007
TAR	201903
TAR	202008

Answer

If version = “ACT” an DATE is not current year i.e 07-2020 to 06-2021 then replace ACT with ACTA

You want a case expression:

select (case when version = 'ACT' and
                  extract(year from date_add(date, interval 6 month)) = extract(year from date_add(current_date, interval 6 month))
             then 'ACTA'
             else version
        end)

The logic is to add six months and extract the year to get the fiscal year. Do the same for the current date. When these are the same, then the date column is in the current fiscal year.