CODEWITHSUNDEEP
sqldatedatetimecastinggoogle-bigquery
Chique_Code
Chique_Code

Reputation: 1530

Invalid datetime string when CAST As Date

I have Time column in BigQuery, the values of which look like this: 2020-09-01-07:53:19 it is a STRING format. I need to extract just the date. Desired output: 2020-09-01.

My query:

SELECT
    CAST(a.Time AS date) as Date
from `table_a`

The error message is: Invalid datetime string "2020-09-02-02:17:49"

Upvotes: 0

Views: 6511

Answers (3)

Black_Fogg
Black_Fogg

Reputation: 38

select STR_TO_DATE('2020-09-08 00:58:09','%Y-%m-%d') from DUAL;

or to be more specific as your column do as:

select STR_TO_DATE(a.Time,'%Y-%m-%d') from `table_a`;

Note: this format is applicable where mysql is supported

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

How about just taking the left-most 10 characters?

select substr(a.time, 1, 10)

If you want this as a date, then:

select parse_date('%Y-%m-%d', substr(a.time, 1, 10))

Upvotes: 1

rtenha
rtenha

Reputation: 3616

You could also use the parse_datetime(), then convert to a date.

with temp as (select '2020-09-02-02:17:49' as Time)
select 
  date(parse_datetime('%Y-%m-%d-%T',Time)) as new_date
from temp

Upvotes: 2

Related Questions