CODEWITHSUNDEEP
sqlgoogle-bigquery
Natalia M
Natalia M

Reputation: 83

BigQuery doesn't convert some string values to date

My table has its dates in string format. I'm trying to transform them to date, but BQ is not accepting some values and I can't find the reason.

What I've tried:

PARSE_TIMESTAMP('%Y%m%d', CLIENTE.dtnasc)

PARSE_TIMESTAMP('%Y%m%d', cast( CLIENTE.dtnasc as string))

CAST(CLIENTE.dtnasc AS DATE)

Error example (it's not only this value):

Failed to parse input string "1991-02-11 00:00:00"

How the date is without transfomartion:

2000-01-01 00:00:00 (string)

Upvotes: 0

Views: 581

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

Consider below option (the simplest I can think of, having that your string column matches already format of timestamp - 2000-01-01 00:00:00 (string))

SELECT DATE(TIMESTAMP(CLIENTE.dtnasc))

Upvotes: 1

Sergey Geron
Sergey Geron

Reputation: 10152

Try this:

SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '1991-02-11 00:00:00')

SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', CLIENTE.dtnasc)
FROM mytable

Upvotes: 1

Related Questions