DATEDIFF for 2 date columns

Get Help
1

Hey!

I'm trying to use the DATEDIFF function on the following piece of code, just to calculate the difference between the two date columns returned. For some reason, I am getting an error.

Metabase's own sample dataset and sample code work with the DATEDIFF function, so not sure where I'm going wront.

31
311168×620 59 KB

2

Hi @miri
Without knowing the database type you are querying, then it's difficult to help.
And without you showing the error, then it's difficult to know what the problem is.
It looks like you are using BigQuery, where the function is called DATE_DIFF:
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#date_diff

3

hey thank you! this is what happens

55
551260×494 43 KB

4

@miri Your query is invalid because you have 2 select-clauses.

5

++ i'm on bigquery

6

11
111184×652 70.2 KB

i've tried multiple variations of the code + the placement but can't seem to get the syntax right..

7

@miri You are missing a comma at the end of mincase in your select-clause.

8

25
251179×518 82.4 KB

400 Bad Request POST https://www.googleapis.com/bigquery/v2/projects/co-7465a/queries { "code" : 400, "errors" : [ { "domain" : "global", "location" : "q", "locationType" : "parameter", "message" : "No matching signature for function DATE_DIFF for argument types: TIMESTAMP, STRING, DATE_TIME_PART. Supported signatures: DATE_DIFF(DATE, DATE, DATE_TIME_PART); DATE_DIFF(DATETIME, DATETIME, DATE_TIME_PART); DATE_DIFF(TIMESTAMP, TIMESTAMP, DATE_TIME_PART) at [6:1]", "reason" : "invalidQuery" } ], "message" : "No matching signature for function DATE_DIFF for argument types: TIMESTAMP, STRING, DATE_TIME_PART. Supported signatures: DATE_DIFF(DATE, DATE, DATE_TIME_PART); DATE_DIFF(DATETIME, DATETIME, DATE_TIME_PART); DATE_DIFF(TIMESTAMP, TIMESTAMP, DATE_TIME_PART) at [6:1]", "status" : "INVALID_ARGUMENT" }

9

@miri All your questions have nothing to do with Metabase, but just incorrect queries.
Try using the BigQuery Console - perhaps it's better at correcting bad syntax.
It says that mincase is seen as a string instead of timestamp, so cast it manually.

10

i realise - thank you for the bigquery console suggestion, actually works!