BigQuery Legacy SQL: Convert String to Date

Question

looking to convert a string of the form '04-OCT-16' to a timestamp. Preferably in Legacy SQL.

Answer 1

If you want to use Legacy SQL, a possible solution is to map the “month” in string format with the numbers and then you can use the TIMESTAMP() function in Legacy SQL. One example is using the REGEXP_REPLACE() function to map all the months one by one:

SELECT TIMESTAMP( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE('04-JAN-16','JAN','01'), 'FEB', '02'), 'MAR', '03'), 'ABR', '04'), 'MAY', '05'), 'JUN', '06'), 'JUL', '07'), 'AUG', '08'), 'SEP', '09'), 'OCT', '10'), 'NOV', '11'),'DEC', '12') AS YOURTIMESTAMP;

You can also create a table in order to do the mapping. In this case you will need to join the two tables and use the function REGEXP_EXTRACT(). In my case, I have a table called “month_mapping” for mapping:

MONTH_STRING  MONTH_NUMBER
JAN           01
FEB           02
MAR           03
ABR           04
MAY           05
JUN           06
JUL           07
AUG           08
SEP           09
OCT           10
NOV           11
DEC           12

and I have a column with string of the form '04-OCT-16' as “test” in the table “test” in the dataset “my_test”, then I use this query to do the mapping:

SELECT
  REGEXP_REPLACE(test,REGEXP_EXTRACT(test,r'.\-([a-zA-Z]+)\-.'), MONTH_NUMBER)
FROM
  my_test.test a
JOIN
  my_test.month_mapping b
ON
  REGEXP_EXTRACT(a.test,r'.\-([a-zA-Z]+)\-.') = b.MONTH_STRING

If you have further questions about Legacy SQL, there is a complete documentation about Legacy SQL Functions and Operators in Google Doc.