Question

Parsing/Formatting a Microsoft Date

  • 2 December 2015
  • 3 replies
  • 120 views

I have some dates stored as integers using the Microsoft date format (days since December 30, 1899: https://msdn.microsoft.com/en-us/library/82ab7w69.aspx )



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: http://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html

* MySQL: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

* Postgres: https://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL

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.

Reply