How to get YYYYMM format string from date column in BigQuery

How can I get YYYYMM format from date column in BigQuery ?

BigQuery(BQ) is very useful for data analysis or processing.
It is good at handling huge data. It returns summary result in short time.
We can use SQL in BigQuery. So it is familiar to RDB user.

And BQ has date type.
So you may want to extract YYYYMM year-month data from date column.

But how can we get YYYYMM format data ?

So today I will introduce about "How to get YYYYMM format from date column in BigQuery".

How to get YYYYMM format from date column in BigQuery

In order to to get YYYYMM format from date column in BigQuery, there are some solution.

I will introduce each of them.

SUBSTR

SUBSTR() if function that extracts part of text.

So we convert date data to text(string) data and use this function.

with t as(
    SELECT DATE("2021-12-08") as date_col
)
SELECT
CONCAT(SUBSTR(CAST(date_col as string),0,4), SUBSTR(CAST(date_col as string),6,2))
FROM t

Result

202112

With using date data 2021-12-08, change it to String type.
Then take first 4 characters and take 2 characters from 6th character.
And concatenate them to YYYYMM format.

String functions  |  BigQuery  |  Google Cloud

EXTRACT

EXTRACT is function for extracting.

We use it like EXTRACT([part name] FROM [date column]).

with t as(
    SELECT DATE("2021-02-08") as date_col
)
SELECT
CONCAT(
    CAST(EXTRACT(YEAR FROM date_col) as string),
    LPAD(CAST(EXTRACT(MONTH FROM date_col) as string), 2, "0")
)
FROM t

Result

202112

Get year value by EXTRACT(YEAR FROM ...), and get month value by EXTRACT(MONTH FROM ...).

It returns int type. So in order to get 2 digits month value, we use LPAD.

Timestamp functions  |  BigQuery  |  Google Cloud

FORMAT_TIMESTAMP is easiest.

We can use it like FORMAT_TIMESTAMP([format string]], [date column]) .

with t as(
    SELECT DATE("2021-02-08") as date_col
)
SELECT
FORMAT_TIMESTAMP("%Y%m", date_col)
FROM t

Result

202112

"%Y%m" means YYYYMM format. So it convert date type data to specific format text.

Timestamp functions  |  BigQuery  |  Google Cloud

Conclusion

Today I explained about "How to get YYYYMM format from date column in BigQuery".

In order to get YYYYMM format, we have these solutions below.

I recommend FORMAT_TIMESTAMP.

Like FORMAT_TIMESTAMP("%Y%m", date_col), we can set format easily.

In order to get YYYYMM format, FORMAT_TIMESTAMP may be suitable.

To get MM value, EXTRACT(MONTH FROM ...) will be better.