BigQuery PARSE_TIMESTAMP Failed to Parse String

Question

I'm trying to convert a string into timestamp format in BigQuery.
The source has all dates in the format "10/15 11:59 PM".

Here's what I'm trying as a proof of concept:

SELECT
PARSE_TIMESTAMP(
'%d/%m/%y %I:%M %p',
  CONCAT(SPLIT('10/15 5:00 PM', ' ')[offset(0)] 
    ,'/',FORMAT_DATE('%y',CURRENT_DATE()),' '
    ,SPLIT('10/15 5:00 PM', ' ')[offset(1)],' '
    ,SPLIT('10/15 5:00 PM', ' ')[offset(2)]
  )
)

The string this generates is "10/15/18 5:00 PM" which looks correct to me, but when I run it I get the error:

Query Failed Error: Failed to parse input string "10/15/18 5:00 PM"

Any ideas what else I could try to get this formatted as a timestamp?

Thanks!

Bobbylank · Accepted Answer · 2018-10-16 13:40:41Z

1

I think you were almost there but your day and month were the wrong way round so it was trying to get the 15th month.

SELECT
PARSE_TIMESTAMP(
'%m/%d/%y %I:%M %p',
  CONCAT(SPLIT('10/15 5:00 PM', ' ')[offset(0)] 
    ,'/',FORMAT_DATE('%y',CURRENT_DATE()),' '
    ,SPLIT('10/15 5:00 PM', ' ')[offset(1)],' '
    ,SPLIT('10/15 5:00 PM', ' ')[offset(2)]
  )
)

answered Oct 16, 2018 at 13:40

Bobbylank

1,96010 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikhail Berlyant · Accepted Answer · 2018-10-16 15:19:25Z

0

Below is for BigQuery Standard SQL

SELECT
PARSE_TIMESTAMP('%Y/%m/%d %I:%M %p', 
  CONCAT(FORMAT_DATE('%Y/',CURRENT_DATE()),'10/15 5:00 PM')
)

answered Oct 16, 2018 at 15:19

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Collectives™ on Stack Overflow

BigQuery PARSE_TIMESTAMP Failed to Parse String

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related