CODEWITHSUNDEEP
sqlgoogle-bigquerygoogle-cloud-platformtype-conversion
cshin9
cshin9

Reputation: 1490

Why doesn't CAST( AS ) sometimes work on BigQuery?

I tried two queries:

SELECT CASE WHEN CAST(CURRENT_TIMESTAMP() AS DATE) = CURRENT_DATE() THEN 1 ELSE 0 END;

and

SELECT CASE WHEN DATE(CURRENT_TIMESTAMP()) = CURRENT_DATE() THEN 1 ELSE 0 END;

The first query fails in Legacy SQL but not in Standard SQL, while the second query works. (Standard SQL is currently not covered by SLA.)

Upvotes: 0

Views: 196

Answers (2)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

There are two problems with first query in Legacy SQL:

  1. CAST(... AS DATE) in Legacy SQL only works on strings, while CURRENT_TIMESTAMP returns TIMESTAMP type

  2. CURRENT_DATE in Legacy SQL is misleadingly returns STRING, not DATE :(

Both of these problems are indeed fixed with Standard SQL

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Legacy SQL has limited support for DATE. For more information, see Civil time in legacy SQL

So, if you need to be in Legacy SQL - you should use second query in your case with DATE() function

Upvotes: 1

Related Questions