BigQuery convert Month Year column to timestamp or date

user_15987774 0
Viewed 367

I have a table

Year_month     amount
08-2021         100
09-2021         200
10-2021         300

I want to add a column Month to be

Year_month   Month         amount
08-2021    01-08-2021         100
09-2021    01-09-2021         200
10-2021    01-10-2021         300
google-bigquery google-cloud-platform
2 Answers

You can use Parse date function

SELECT PARSE_DATE("%m-%Y", "08-2021")
user_113725930

It depends of the type of data you have. check below code:

with sodata as (
  select '08-2021' as Year_month_string,  PARSE_DATE("%m-%Y", "08-2021") as Year_month_date, 100 amount,
  union all 
  select '09-2021',PARSE_DATE("%m-%Y", "09-2021"), 200
  union all 
  select '10-2021',PARSE_DATE("%m-%Y", "10-2021"), 300
  
)
SELECT Year_month_string,FORMAT('01-%s',Year_month_string) as using_format,FORMAT_DATE("%d-%m-%Y", Year_month_date) as using_format_date,amount from sodata

output

Row Year_month using_format using_format_date amount
1 08-2021 01-08-2021 01-08-2021 100
2 09-2021 01-09-2021 01-09-2021 200
3 10-2021 01-10-2021 01-10-2021 300

To see more details about the functions used please go to this links:

user_145515670
Related