How to Use Math in Date Values
Better understand how Datameer does math operations over date objects.
By default Datameer displays date values like
MMM d, yyyy HH:mm:ss AM/PM, without milliseconds, e.g.
May 1, 2018, 12:00:00 AM. However, the math with date values is being done in ms, so under the hood when you compare two dates in a join operation or add/subtract # of days, the machine works with the integer values of milliseconds.
STARTOFMONTH() function result is displayed by default as the start of the first day of the month, e.g. May 1, 2018, 12:00:00 AM, which is
ENDOFMONTH() function result is displayed by default as the end of the last day of the month, e.g. Apr 30, 2018, 11:59:59 AM, which is
When you generate some data using a formula e.g.
ENDOFMONTH(#A)+1s. it shows the result as May 1, 2018, 12:00:00 AM, which looks similar to May 1, 2018, 12:00:00 AM generated by STARTOFMONTH() function.
However, if fact this is
2018-05-01 00:00:00.999 from
2018-05-01 00:00:00.000 from
. It would be obviously when you try to join two sheets using these date values as there are no results.
In order to avoid any mismatches in date value calculation results, it is recommended to use the ROUNDTIME function whenever possible to ensure that dates are in the expected format.
Alternatively you could also use the FORMATDATE function in the formula
formatdate(<Sheet Name>;"yyyy-MM-dd HH:mm:ss.SSS") to check if the millisecond value is expected.