CODEWITHSUNDEEP
sqlgoogle-bigquery
bunny
bunny

Reputation: 354

Convert a day, date and timestamp string in the format YYYY-MM-DD HH:MM:SS in SQL

Is there any way to convert "A day, date and timestamp string in the format YYYY-MM-DD HH:MM:SS” using standard SQL syntax in BigQuery.

For eg.:

Fri, 31 Aug 2018 13:00:57 +0000

Here is what I tried to come up with:

SELECT PARSE_DATETIME('%a,%d %b %Y %E#S' , 'Fri, 31 Aug 2018 13:00:57 +0000')

Upvotes: 0

Views: 705

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below example for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Fri, 31 Aug 2018 13:00:57 +0000' ts_string
)
SELECT 
  ts_string, 
  PARSE_TIMESTAMP('%a, %d %b %Y %X %z' , ts_string) ts_timestamp,
  DATETIME(PARSE_TIMESTAMP('%a, %d %b %Y %X %z' , ts_string)) ts_datetime
FROM `project.dataset.table`

with result 

Row ts_string                         ts_timestamp              ts_datetime  
1   Fri, 31 Aug 2018 13:00:57 +0000   2018-08-31 13:00:57 UTC   2018-08-31T13:00:57

Upvotes: 3

Related Questions