Bigquery convert ''Jan 30 2016 12:00AM" to date

0
votes

I'm attempting to convert a string to a timestamp within Bigquery. The question is really simple, how do I convert this string Jan 30 2016 12:00AM or Aug 9 2015 12:00AM to date?

I have tried referring to the Bigquery documentation but it didn't seem to work: (https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#parse_date) select PARSE_DATE('%b %e %E4Y', 'Jan 30 2016 12:00AM') select PARSE_DATE('%b %e %E4Y', 'Aug 9 2015 12:00AM')

2
sqlgoogle-bigquery

2 Answers

1
votes

Your input string is actually a timestamp, not a date, because it has a time component at the end. So, consider using PARSE_TIMESTAMP instead:

SELECT PARSE_TIMESTAMP('%b %e %E4Y %I:%M%p', 'Jan 30 2016 12:00AM') AS ts_out
FROM yourTable;

This would return a timestamp based on the input string. If you really want a date, you could cast the above to date, or you could just use LEFT on the string input to restrict to only the date portion:

SELECT PARSE_DATE('%b %e %E4Y', LEFT('Jan 30 2016 12:00AM', 11)) AS ts_out
FROM yourTable;
0
votes

Below is for BigQuery Standard SQL 

SELECT DATE(PARSE_TIMESTAMP('%b %e %Y %R%p', ts))

You can test it with dummy data as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Jan 30 2016 12:00AM' ts UNION ALL
  SELECT 'Aug  9 2015 12:00AM' UNION ALL
  SELECT 'Aug 9 2015 12:00AM'
)
SELECT ts, DATE(PARSE_TIMESTAMP('%b %e %Y %R%p', ts)) date_from_ts
FROM `project.dataset.table`

with result 

Row ts                  date_from_ts     
1   Jan 30 2016 12:00AM 2016-01-30   
2   Aug 9 2015 12:00AM  2015-08-09   
3   Aug 9 2015 12:00AM  2015-08-09