Brtdku

I'm trying to do two things in BigQuery, but I'm having difficulties doing so.

I'd like to do two things :

1. Convert my date columns to Date format (it is currently in int64, with 43379 as an example)


2. Cast my columns Delivered_Cost and Actual_Cost to float (they are currently with string type) - As when there is a null value, there is - instead of 0. When you cast to float, do these -s automatically change to 0 or do I have to update that first?

I don't have much experience this, and I've been having difficulties looking for a solution online so I'd love any help! I'm having difficulties casting and displaying data from my table at the same time.

Thank you!

SELECT * FROM TABLE1

CAST(Delivered_Cost as float) 

1. Convert my date columns to Date format (it is currently in int64, with 43379 as an example)

Use function PARSE_DATE() :

PARSE_DATE(Delivered_Date, '%Y-%m-%d')

The following doc lists the supported formats.

2. Cast my columns 'Delivered_Cost' and 'Actual_Cost' to float (it is currently with string type)

Your syntax with CASE() is OK ; you could also use shortcut method FLOAT(). However if your string does not successfully maps to a float (like - alone), a runtime error will occur. You could use SAFE_CAST() to ignore conversion error, but that might also lead to ignoring relevant errors. Hence, you would better use REPLACE().

Here is your query :

SELECT

    PARSE_DATE(Delivered_Date, '%Y-%m-%d') AS Delivered_Date,

    FLOAT(REPLACE(Delivered_Cost, '-', '0')) AS Delivered_Cost,

    FLOAT(REPLACE(Actual_Cost, '-', '0')) AS Actual_Cost  

FROM MYTABLE





FLOAT(Delivered_Cost)

Below is for BigQuery Standard SQL

#standardSQL

SELECT 

  DATE_FROM_UNIX_DATE(date_column_as_number_of_days_since_epoch) date_since_epoch,

  IFNULL(SAFE_CAST(Delivered_Cost AS FLOAT64), 0.0) AS Delivered_Cost,

  IFNULL(SAFE_CAST(Actual_Cost AS FLOAT64), 0.0) AS Actual_Cost

FROM `project.dataset.table`   

You can test, play with it using dummy data as below

#standardSQL

WITH `project.dataset.table` AS (

  SELECT 

    43397 AS date_column_as_number_of_days_since_epoch,  

    '123' AS Delivered_Cost,

    ' - ' AS Actual_Cost 

)

SELECT 

  DATE_FROM_UNIX_DATE(date_column_as_number_of_days_since_epoch) date_since_epoch,

  IFNULL(SAFE_CAST(Delivered_Cost AS FLOAT64), 0.0) AS Delivered_Cost,

  IFNULL(SAFE_CAST(Actual_Cost AS FLOAT64), 0.0) AS Actual_Cost

FROM `project.dataset.table`   

with result as

Row date_since_epoch    Delivered_Cost  Actual_Cost  

1   2088-10-25          123.0           0.0     

Note: I am assuming that 43379 which you use as an example in your question is actually a number of days since epoch - as this is most reasonable from my point assumption - let us know if this is something else, so I will adjust answer respectively