How to convert an int to DateTime in BigQuery

I have an INT64 column called “Date” which contains many different numbers like: “20210209” or “20200305”. I want to turn those numbers into a date with this format: MM-YYYY (so in these cases, 02-2021 and 03-2020). Ultimately I want to sum all the data in each month together. The problem is that BigQuery can’t convert INT64 to date, only to strings. I’m not sure if I should convert to a string and then to a date or if there is a better way.

Answer

Although converting to a string then a date both works and is very concise, over large enough numbers of rows (which may be the case in Big Query) you may be better off using integer maths and using DATE(year, month, day)…

https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date

SELECT
  DATE(
    DIV(    20210209        , 10000),   -- Which gives 2021
    DIV(MOD(20210209, 10000),   100),   -- Which gives   02
        MOD(20210209,   100)            -- Which gives   09
  )