Changing Month and Day in BigQuery скачать в хорошем качестве

Changing Month and Day in BigQuery

Learn how to change only the month and day in BigQuery's DATETIME fields while preserving the year. Simple steps and code included! --- This video is based on the question https://stackoverflow.com/q/64361483/ asked by the user 'James' ( https://stackoverflow.com/u/12345378/ ) and on the answer https://stackoverflow.com/a/64361622/ provided by the user 'Mikhail Berlyant' ( https://stackoverflow.com/u/5221944/ ) 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: Change month and day only in 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. --- Changing Month and Day in BigQuery: A Simple Guide Are you looking to modify just the month and day of a timestamp in BigQuery without altering the year? If so, you've come to the right place. In this guide, we will explore how to achieve this using SQL syntax suitable for BigQuery. It’s a straightforward process once you know the right functions to use. Let's dive in! The Problem Imagine you have a table in BigQuery that contains a DATETIME field representing birth dates. Here’s a snippet of what that might look like: [[See Video to Reveal this Text or Code Snippet]] In this example, you may wish to change the month and day of some entries while keeping the year intact. For instance, you want to update the second row to reflect a new date of 2015-04-01T00:00:00. You may be accustomed to using DATEFROMPARTS in SQL Server, but how can you achieve the same result in BigQuery? The Solution BigQuery provides an alternative way to manage this using the EXTRACT function. Below, we will break down the process step-by-step. Step 1: Extracting the Year To start, you'll need to extract the year from your existing BirthYear timestamp. You can do this with the EXTRACT function: [[See Video to Reveal this Text or Code Snippet]] Step 2: Combining Year with New Month and Day Once you have the year, you can use the DATE function to construct a new date. In your case, to change to April 1st, you would do the following: [[See Video to Reveal this Text or Code Snippet]] Step 3: Updating the DATETIME Field If you wish to convert this result back into a DATETIME format (since BigQuery might require it to maintain the same type), you can wrap the previous result with the DATETIME function: [[See Video to Reveal this Text or Code Snippet]] Full Update Query If you were to write the full update query to change the month and day for entries where needed, it would look like this: [[See Video to Reveal this Text or Code Snippet]] Summary To recap, changing just the month and day in a BigQuery DATETIME field is achievable using the EXTRACT function to get the year and then combining it with the new month and day using the DATE function. This method helps you maintain the integrity of your data while applying necessary updates. By following the steps outlined above, you can effectively manipulate dates in BigQuery without the complexities that may arise from switching SQL dialects. Happy querying!