CODEWITHSUNDEEP
dategoogle-bigquerylegacy
denim
denim

Reputation: 463

BigQuery Legacy SQL: Convert String to Date

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

Upvotes: 1

Views: 2161

Answers (3)

AliveToLearn
AliveToLearn

Reputation: 41

this works in BigQuery legacy SQL for a date 'string' YYYYMMDD:

STRFTIME_UTC_USEC((PARSE_UTC_USEC (CONCAT(left(string,4),'-',left(right(string, 4),2),'-',right(string,2),' 00:00:00'))),"%Y-%m-%d") as myDateField

Notice the space in ' 00:00:00'

Conversion of string to date is a pain in legacy sql, but it's much simpler in Standard SQL.

Upvotes: 0

enle lin
enle lin

Reputation: 1714

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.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

looking to convert a string of the form '04-OCT-16' to a timestamp

it is really recommended to use BigQuery Standard SQL whenever possible 

#standardsql
SELECT PARSE_TIMESTAMP('%d-%b-%y', '04-OCT-16')

Upvotes: 2

Related Questions