CODEWITHSUNDEEP
google-bigquery
Jaeger_007
Jaeger_007

Reputation: 23

DATE_DIFF BigQuery

DATE_DIFF(CURRENT_DATE('Europe/Paris'), 365, DAY)

Returns me error

No matching signature for function DATE_DIFF for argument types: DATE, INT64, 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)

How to correct this ?

My regards,

Upvotes: 0

Views: 994

Answers (2)

Cylldby
Cylldby

Reputation: 1978

Aren't you looking for the function DATE_SUB?

DATE_SUB(CURRENT_DATE('Europe/Paris'), INTERVAL 365 DAY)

DATE_SUB substracts 365 days from the current date in Paris and returns the corresponding DATE.

DATE_DIFF in contrast returns the number (therefore an INT) of DATE_TIME_PART between 2 dates.

See here for reference.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Use below instead

SELECT DATE_SUB(CURRENT_DATE('Europe/Paris'), INTERVAL 365 DAY)

As a shortcut, below also works

SELECT CURRENT_DATE('Europe/Paris') - 365

Upvotes: 1

Related Questions