Date column which contains null values as well-CodePudding

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))

CodePudding user response：

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:

CodePudding user response：

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:

[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