CODEWITHSUNDEEP
sqlgoogle-bigquery
Lukasz
Lukasz

Reputation: 359

How to easily get year and month with leading zero in BigQuery?

I have a snapshot date in the following format: 2021-06-28 and I would like to have it like that: 202106. So the first four digits is year and next two is the month with leading zero. I tried this code:

concat(extract(year from snapshot_date), extract(month from snapshot_date))

but I have in return: 20216, without leading zero. How can I easily get it without CASE statement?

Upvotes: 2

Views: 4224

Answers (4)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173056

Assuming snapshot_date is of date data type - you can use recently introduced Format clause for CAST as in below example

select cast(snapshot_date as string format 'YYYYMM')
from `project.dataset.table`

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270573

As a note, you can return the value as a number and just use multiplication:

(extract(year from snapshot_date) * 100 + extract(month from snapshot_date)) as yyyymm

Upvotes: 0

Erfan Bashar
Erfan Bashar

Reputation: 149

if "snapshot_date" is in date format, then this should work

select to_char(snapshot_date, 'yyyymm') from table_name

Upvotes: 0

Sergey Geron
Sergey Geron

Reputation: 10212

Try format_date:

select format_date("%Y%m", snapshot_date) 
from mytable

Upvotes: 4

Related Questions