CODEWITHSUNDEEP
google-bigquery
Shivi Bhatia
Shivi Bhatia

Reputation: 189

Date Difference between 3 dates

I am calculating difference between 3 dates using legacy sql. The idea is to find difference in 2 days and when the second date is null should take the third date.

Have built the query below but the error is 

SELECT
asset_tag , receive_date  , first_time , second_time , location , 
location_country ,
m_n12, display,
Datediff(  Case When second_time  is null Then first_time
When first_time  is null Then second_time
When first_time  > second_time  Then second_time
Else first_time
END as first_date ,receive_date )
FROM [abc_table]
where type in ('Laptop', 'Monitor', 'Desktop')
and receive_date >'2017'

Encountered " "AS" "as "" at line 8, column 9. Was expecting: ")"

Upvotes: 0

Views: 362

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

Remove the alias from the CASE expression:

DATEDIFF(CASE WHEN second_time IS NULL THEN first_time
              WHEN first_time IS NULL THEN second_time
              WHEN first_time > second_time THEN second_time
              ELSE first_time END, receive_date)

An alias might make sense on the entire function call to DATEDIFF, but it does not belong inside the function call itself.

Upvotes: 1

Related Questions