Search code examples
sqlgoogle-bigquerydata-analysis

CAST date to number in order to do aggregate functions

So I want to make my log_work_started column that currently is in Date format to a numeric value in order to perform calculations later on.

I am using the next query:

    SELECT
  Log_Work_started AS date
FROM
  `data_mkt.marketing_jira`,
  (
    SELECT
      Log_Work_started AS date,
      CAST(Log_Work_started AS FLOAT64) AS Log_Work_started_Float64
    FROM
      `data_mkt.marketing_jira`
    WHERE
      Log_Work_started BETWEEN "2023-04-01" AND "2023-04-30"
  ) AS test

But I got the next error within BigQuery: "Invalid cast from DATE to FLOAT64"

My expected output is to be able to perform a COUNT formula to count the number of days in the log_work_started column

Does anyone know how can I achieve this?

Solution

  • You can use this syntax if you only want to count the number of DAYS.

    DATE_DIFF(DATE '2023-04-01', DATE '2023-04-30', DAY) AS days_diff;