
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

Advantage to read
You can understand "How to get YYYYMM format from date column in BigQuery". Then you don't have to concern about converting date record.
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.
Methods to extract part of date
- SUBSTR
- EXTRACT
- FORMAT_TIMESTAMP
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.
SQL
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
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])
.
SQL
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
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])
SQL
with t as( SELECT DATE("2021-02-08") as date_col ) SELECT FORMAT_TIMESTAMP("%Y%m", date_col) FROM t
Result
"%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.
Methods to extract part of date
- SUBSTR
- EXTRACT
- FORMAT_TIMESTAMP
I recommend FORMAT_TIMESTAMP
.
Like FORMAT_TIMESTAMP("%Y%m", date_col)
, we can set format easily.

YYYYMM
format, FORMAT_TIMESTAMP
may be suitable.
MM
value, EXTRACT(MONTH FROM ...)
will be better.There are some other articles about BigQuey.
If you interested in them, please read them.
Read more