BigQuery Legacy SQL：将字符串转换为日期及时间戳（'04-OCT-16'格式）

Got it, let's walk through exactly how to handle this in BigQuery Legacy SQL—since that's your priority. Strings in the format '04-OCT-16' can be converted to both date and timestamp types using built-in functions, no fancy workarounds needed.

1. Convert to Date Type #

First, we'll use PARSE_TIMESTAMP to turn the string into a timestamp, then extract the date component with the DATE() function. The format specifiers here match your input perfectly:

• %d: Two-digit day (04)
• %b: Abbreviated month name (OCT)
• %y: Two-digit year (16)

Here's the query:

SELECT DATE(PARSE_TIMESTAMP('%d-%b-%y', '04-OCT-16')) AS converted_date;

This will return a DATE type value like 2016-10-04.

2. Convert to Timestamp Type #

If you need a full timestamp instead of just a date, you can use PARSE_TIMESTAMP directly—it returns a TIMESTAMP type by default. By default, this assumes the input is in UTC, but you can specify a timezone if needed:

Basic UTC Timestamp #

SELECT PARSE_TIMESTAMP('%d-%b-%y', '04-OCT-16') AS converted_timestamp;

This gives you 2016-10-04 00:00:00 UTC.

Timestamp with Specific Timezone #

If your input string is in a different timezone (e.g., Eastern Standard Time), add the timezone as a third parameter:

SELECT PARSE_TIMESTAMP('%d-%b-%y', '04-OCT-16', 'America/New_York') AS converted_timestamp_est;

This will adjust the timestamp to UTC based on the specified timezone.

Note on Two-Digit Years #

BigQuery Legacy SQL treats two-digit years (%y) as follows:

• Values 00-69 map to 2000-2069
• Values 70-99 map to 1970-1999
  This is the standard behavior, but keep it in mind if you're working with dates outside that range.

内容的提问来源于stack exchange，提问作者denim