CODEWITHSUNDEEP
sqldategoogle-bigquery
Ram
Ram

Reputation: 99

Date column which contains null values as well

i have column called startup_date which defined as STRING datatype in bigquery

which contains value like "2001-09-09 02:19:38.0 UTC" and null values as well

please help to use convert function to fetch only date value not hours and mins

used below function and getting invalid datetime string error message

EXTRACT(date FROM datetime(CASE when startup_date = '' THEN NULL ELSE startup_date END))

Upvotes: 1

Views: 4598

Answers (2)

Jose Gutierrez Paliza
Jose Gutierrez Paliza

Reputation: 1428

You can try substr[1] from 1 to 10 to get the date, and then you can use the safe.parse_date function[2].

SELECT safe.parse_date('%Y-%m-%d', substr(startup_date, 1, 10)) AS startup_date FROM you_dataset.your_table

It returns this:

enter image description here

[1] https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#substr

[2] https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#parse_date

Upvotes: 0

Alvaro
Alvaro

Reputation: 963

The DATE and TIMESTAMP functions do exactly what you are looking for. If you have a STRING column where its format is like TIMESTAMP, you can simply apply it. Then, DATE will extract just the date and it takes care of the NULL values.

WITH my_data AS 
(
  SELECT TIMESTAMP("2001-09-09 02:19:38.0 UTC") AS startup_date UNION ALL
  SELECT NULL UNION ALL
  SELECT "2021-10-10 07:29:30.0 UTC"
)

SELECT DATE(startup_date) as date FROM my_data

returns: enter image description here

Upvotes: 0

Related Questions