CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
Christoph
Christoph

Reputation: 1377

big query if and dates error

I am creating a if function to check if a string date matches the current year. My next step would be doing a datesub or just showing the regular date.

I am getting following error when trying this IF function in BQ.

Error in query string: No matching signature for function IF for argument types:BOOL, DATE, BOOL. Supported signature: IF(BOOL, ANY, ANY) at [3:1]

 IF(FORMAT_DATETIME("%Y",CURRENT_DATETIME())= SUBSTR(date, 0, 4), 
 DATE(cast(SUBSTR(date, 0, 4) AS INT64),cast(SUBSTR(date, 4, 2) AS INT64),cast(SUBSTR(date, 6, 2) AS INT64)), 
 FALSE)

Following does work:

 IF(FORMAT_DATETIME("%Y",CURRENT_DATETIME())= SUBSTR(date, 0, 4),
 TRUE, 
 FALSE)

How do I get this working in BQ (standard sql)?

The docs say this which I do not understand and find no clear answers on google from. https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#conditional-expressions

true_result and else_result must be coercible to a common supertype.

Thanks

Upvotes: 0

Views: 2158

Answers (2)

schlebe
schlebe

Reputation: 3715

I think that Google SQL refuse to mix return value of type DATE and BOOLEAN !

In your example, the third argument of IF(BOOL, ANY, ANY) is equal to FALSE but it must be of type DATE or must be NULL.

IF(FORMAT_DATETIME("%Y",CURRENT_DATETIME())= SUBSTR(date, 0, 4)
  ,DATE(cast(SUBSTR(date, 0, 4) AS INT64)
       ,cast(SUBSTR(date, 4, 2) AS INT64)
       ,cast(SUBSTR(date, 6, 2) AS INT64)
       )
  ,NULL
  )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

I think you just want case:

(CASE WHEN FORMAT_DATETIME('%Y', CURRENT_DATETIME()) =
           SUBSTR(date, 0, 4), 
      THEN DATE(cast(SUBSTR(date, 0, 4) AS INT64), cast(SUBSTR(date, 4, 2) AS INT64),cast(SUBSTR(date, 6, 2) AS INT64))
 END)

I think you could simplify this a bit. For instance:

(CASE WHEN FORMAT_DATETIME('%Y', CURRENT_DATETIME()) = SUBSTR(date, 1, 4), 
      THEN PARSE_DATE('%Y%m%d', date) 
 END)

Upvotes: 1

Related Questions