Скачать или смотреть How to Convert String to DateTime in BigQuery

Learn how to convert a string representation of datetime to actual DateTime format in Google BigQuery efficiently and correctly.
---
This video is based on the question https://stackoverflow.com/q/63988679/ asked by the user 'KayEss' ( https://stackoverflow.com/u/12813881/ ) and on the answer https://stackoverflow.com/a/63989329/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: String to DateTime BigQuery

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Convert String to DateTime in BigQuery

When working with data in Google BigQuery, you might encounter scenarios where date and time are stored as strings. This can pose a challenge when you need to perform operations that require real datetime formats. If you've ever looked at your dataset and thought, "How do I convert these string datetimes into a proper format?" then you've come to the right place.

In this guide, we’re going to cover how to convert strings formatted as datetime in BigQuery into actual datetime objects. This is particularly useful when you’re dealing with a column of data that looks something like this:

"19.03.2020 08:14:13"

"30.04.2020 08:57:45"

Understanding the Problem

You might have used a command similar to the following in your SQL query:

[[See Video to Reveal this Text or Code Snippet]]

However, you may have encountered an error message stating:

[[See Video to Reveal this Text or Code Snippet]]

Why Did This Happen?

The reason for this error is mainly twofold:

Incorrect Date Separator: Your format specifier is not accommodating the period (.) separators between the day, month, and year components.

Year Format: You used %y for the year, which expects a 2-digit year. However, your strings are using a 4-digit format (%Y).

The Solution

To correctly parse string datetimes in BigQuery, follow these two changes:

1. Use the Correct Format Specifier

Change your existing SQL command to use the dot as the separator between date components:

[[See Video to Reveal this Text or Code Snippet]]

2. Simplifying the Query

For further efficiency, you don’t need to specify the hours, minutes, and seconds separately. You can actually replace the last part with %T, which stands for HH:MM:SS:

[[See Video to Reveal this Text or Code Snippet]]

Summary

To convert strings formatted as dates into actual datetime objects in BigQuery, you need to ensure:

The correct separators are used in your format string.

Specify the year as a 4-digit year with %Y.

Implementing these changes will resolve any parsing errors and allow you to work with datetime values effectively. Happy querying! If you have any additional questions, feel free to leave a comment below.