Parsing/Formatting a Microsoft Date

  • 2 December 2015
  • 3 replies

I have some dates stored as integers using the Microsoft date format (days since December 30, 1899: )

Is there a clean way to convert this to a date within Looker in order to calculate weekly totals?

Typical values are 41094, 41103, etc.

3 replies

Userlevel 4

Hi Jason,

One solution that might work is using a date add function. Here it is for Redshift

- dimension: converted_date

sql: dateadd(date,${microsoft_date},'1899-12-30')


Since you are looking for weekly totals you should be okay, but for more granular detail (such as hourly) you may need to verify daylight savings time/timezones.

More information on date add functions:

* Redshift:

* MySQL:

* Postgres:

Thank you. I’m using Greenplum, so the PostgreSQL solution will probably work. 🙂

And, dates already normalized to day, so no worries there.

Here’s my working Greenplum code:

  - dimension: converted_date

sql: TIMESTAMP '1899-12-30' + (${order_date} * INTERVAL '1 days')

TY for the assistance.