How to get YYYYMM format 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.

Then how can we get YYYYMM format data ?

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

Author

Mid-carieer engineer (AI, system). Good at Python and SQL.

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

In 2021-12-08, take first 4 characters and take 2 characters from 6th character. Then concatenate them to YYYYMM format.

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

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.

FORMAT_TIMESTAMP

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

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

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.

There are some other articles about BigQuey.

If you interested in them, please read them.