BigQuery Partition Date | Specific Day

yyyymm is a column in tbl table which is in string format and looks like 202212 which means Dec 2022

When I am using below query, its creating a new column in the tbl_new table by the name sample_date with output as 2022-12-01 .

As you can see i am getting 01 date added which i need it to be 15 . Is there any way, to customize date on my own?

I tried adding %Y%m15 but its not supporting and giving invalid literal error in BigQuery.

Current output: 2022-12-01

Expected Output: 2022-12-15

CREATE or replace TABLE
 project_id.dataset_id.tbl_new
PARTITION BY
 sample_date AS
SELECT
 *,
 PARSE_DATE('%Y%m',yyyymm) AS sample_date
FROM
 project_id.dataset_id.tbl

CodePudding user response：

Using DATE_ADD function, I was able to attain the required result. By default, PARSE_DATE gives 01 as date and using INTERVAL clause, I was able to achieve the desired output.

CREATE or replace TABLE
 project_id.dataset_id.tbl_new
PARTITION BY
 sample_date AS
SELECT
 *,
 ***DATE_ADD(PARSE_DATE('%Y%m',yyyymm), INTERVAL 14 DAY) as sample_date***
FROM
 project_id.dataset_id.tbl