Cast integer to date

low_priority
done
reply
(cristian) #1

Hi all!
I have a dimension that is stored in BQ as an integer.
This is represented in LookML as:
dimension: mydate { type: number sql:${TABLE}.MYDATE;; }

The format is 20190101.

I would like to cast this to a date format. In BQ I would do something like this:
PARSE_DATE('%Y%m%d', FORMAT('%08d',mydate))

So, I change the dimension to this:
dimension: mydate { type: date sql:PARSE_DATE('%Y%m%d', FORMAT('%08d',${TABLE}.MYDATE));; }
But it doesnt work. I get this error message:

“Failed to retrieve data - No matching signature for function FORMAT_TIMESTAMP for argument types: STRING, DATE, STRING. Supported signature: FORMAT_TIMESTAMP(STRING, TIMESTAMP, [STRING]) at [4:24]”

When I look at the generated SQL I see that FORMAT_TIMESTAMP is added because of the applications time zone.

Any ideas what I can do?

Cheers,
Cris

0 Likes

(Izzy) #2

Since the function looks like it’s expecting a timestamp, one option would be to just cast it manually as a timestamp in the SQL, wrapping the parse_date() in a timestamp()— Just like:

  dimension: mydate { 
    type: date
    sql:TIMESTAMP(PARSE_DATE('%Y%m%d', FORMAT('%08d',${TABLE}.MYDATE)));; 
    }

There’s a better way to do it, though— Add a datatype: date line:

  dimension: mydate { 
    type: date
    datatype: date
    sql:PARSE_DATE('%Y%m%d', FORMAT('%08d',${TABLE}.MYDATE));; 
    }

BigQuery is particular about the way it treats date and timestamp fields, and I think it has something to do with that. I believe we have some open investigations into ways to improve the way we do date formatting/parsing with BQ. But that should fix it!

1 Like

(cristian) #3

@izzy you’re a champ! Thanks. I just added datatype: date Didn’t even know there was a “datatype”.

0 Likes

(Izzy) #4

Usually, you only have to use it with dimension_groups (where you have to use it every time) but you can use it in situations like this to set Looker back on the right track. Glad it worked!

1 Like