LinkedIn
Twitter
In excel, when you enter a date it often holds that value as a number behind the scenes but to the user presents the familiar date format. 45509 is the number of days since 1900-01-01.
There is a function for this in looker but unfortunately it counts from 1970-01-01
My way around this is to use this as a custom field
cast(datetime_add(date(1900, 01, 01), interval 45509 day) as date)Replace the 45509 value with the name of your date dimension.
Hi I've tried your formula. So my dimension field that's contains numerical values like 44509 is named "Document Date"
cast(datetime_add(date(1900, 01, 01), interval 45509 day) as date)but running into a couple of issues:
1. I don't believe looker can handle the year '1900' - it kept saying it couldn't complete my request at the time. When I changed it to DATE(1970, 01, 01) it was able to load.
After using year 1970, I then created a new field to get the difference between 1900/01/01 and 1970/01/01 in days. Then subtracted it against my Document Date and named it Unix Adjust Date
2. This is where I'm stuck --> my current formula is:
CAST(DATETIME_ADD(DATE(1970,1,1), INTERVAL (Unix Date Adjust) DAY) AS DATE)
However I keep getting errors:
