BigQuery CAST whole string column to date after LEFT-CodePudding

The data came in datetime format but was upload as a string to BigQuery.I want to extract only the date part but not the time part. I try to use a subquery using code:

SELECT CAST(LEFT(SleepDay,9) AS DATE)

but I kept getting: Invalid datetime string "4/12/2016" as an error. Please help me fix it thank you so much.

CodePudding user response：

try use BIGQuery parse_date() function.

SELECT PARSE_DATE('%m/%d/%Y', LEFT(SleepDay,9))

CodePudding user response：

I would not recommend using the LEFT(date_str, 9) function, since you don't want to parse 12/31/2022 as 12/31/202. Instead, parse the date_string and use the PARSE_DATE() function with proper format strings.

WITH
dataset AS (
    SELECT "4/12/2016T12:34:56" as SleepDay
    UNION ALL SELECT "4/15/2016T12:34:56" as SleepDay
),
some_how_parse_the_date_string AS (
    SELECT SPLIT(SleepDay, 'T')[OFFSET(0)] as SleepDay_date_string
    FROM dataset
)
SELECT PARSE_DATE('%m/%d/%Y', SleepDay_date_string) as SleepDay_date
FROM some_how_parse_the_date_string
;