CAST a calculation from dates into INT

user_17301261 0
Viewed 244

I am learning BigQuery, and to practice I wanted to subtract a date from CURRENT_DATE (to calculate how many days since a last log in), but the result appears as a date.

I tried to CAST and SAFE_CAST but it gives me error.

# Gives calculaiton result as a date

    SELECT 
            short_name,
            CURRENT_DATE-last_log_in AS days_wo_log_in,
    FROM `noted-wares-328320.demo.test_date`;

# Error invalid cast from INTERVAL to INT64

    SELECT
            short_name,
            CAST (CURRENT_DATE-last_log_in as INT) AS days_wo_log_in,
    FROM `noted-wares-328320.demo.test_date`;

Table that appears

Is there a way I am missing?

Thank you,

google-bigquery casting
2 Answers

Use DATE_DIFF:

SELECT DATE_DIFF(CURRENT_DATE(), last_log_in, DAY) AS days_wo_log_in
FROM `noted-wares-328320.demo.test_date`;
user_134735250

Another approach

SELECT 
  short_name,
  EXTRACT(DAY FROM CURRENT_DATE-last_log_in) AS days_wo_log_in
FROM `noted-wares-328320.demo.test_date`

with output

enter image description here

user_52219440
Related