Hey Ellie, let’s break down your BigQuery problems step by step to get you sorted out:

That Int64 value (like 43379) looks like an Excel-style serial date, which counts days starting from a fictional 1900-01-01 (with a known leap year bug). To convert it to a proper BigQuery DATE type, you’ll need to adjust for that offset using DATE_ADD with the correct starting date:

SELECT DATE_ADD('1899-12-30', INTERVAL your_date_column INT64 DAY) AS converted_date
FROM TABLE1

The 1899-12-30 base fixes Excel’s incorrect leap year assumption, so your 43379 value will convert to the correct date (in this case, 2018-10-01).

BigQuery won’t automatically turn "-" into 0 when casting to float—you’ll need to handle those values first. Here are two reliable approaches:

Option 1: Use a CASE statement for explicit control #

This lets you clearly define how to handle "-" values:

SELECT
  SAFE_CAST(CASE WHEN Delivered_Cost = '-' THEN '0' ELSE Delivered_Cost END AS FLOAT64) AS delivered_cost_float,
  SAFE_CAST(CASE WHEN Actual_Cost = '-' THEN '0' ELSE Actual_Cost END AS FLOAT64) AS actual_cost_float
FROM TABLE1

Option 2: Replace "-" with "0" directly #

If you’re sure "-" is the only non-numeric value, REPLACE works too:

SELECT
  SAFE_CAST(REPLACE(Delivered_Cost, '-', '0') AS FLOAT64) AS delivered_cost_float,
  SAFE_CAST(REPLACE(Actual_Cost, '-', '0') AS FLOAT64) AS actual_cost_float
FROM TABLE1

I recommend using SAFE_CAST instead of plain CAST because it’ll return NULL for any unexpected non-numeric values instead of throwing an error, keeping your query running smoothly.

Your example query has a syntax error—you can’t just tack CAST onto the end of SELECT * FROM TABLE1. If you want to select all original columns plus your converted cost column, structure it like this:

SELECT
  *,
  SAFE_CAST(REPLACE(Delivered_Cost, '-', '0') AS FLOAT64) AS delivered_cost_float
FROM TABLE1

Or combine all your conversions into one efficient query:

SELECT
  *,
  DATE_ADD('1899-12-30', INTERVAL your_date_column INT64 DAY) AS converted_date,
  SAFE_CAST(REPLACE(Delivered_Cost, '-', '0') AS FLOAT64) AS delivered_cost_float,
  SAFE_CAST(REPLACE(Actual_Cost, '-', '0') AS FLOAT64) AS actual_cost_float
FROM TABLE1

内容的提问来源于stack exchange，提问作者Ellie