Making DateTimes and Numbers work together

The eBMS Platform formula engine is heavily influenced by the Excel formula engine and similar spreadsheet formula systems.

Microsoft Excel does not have a native DateTime type, but rather treats numbers as dates when convenient.

Excel Dates

To illustrate this, type a number into a cell in Excel, and then format that cell as a date. Then try the inverse: enter a date, and then format it as a number.

In Excel dates are simple floating point numbers indicating a number of dates from an Epoch of 12:00 AM on 0/01/1900. If you format the number 0 as a date, this is the date you will see.In eBMS you will see 12:00AM on 30/12/1899 which is the equivalent real date.

True DateTimes

To improve interopration with Database tables, allowing date-specific functionality, reduce confusion, and for various other reasons, the eBMS platform has a true DateTime type. These dates will become numbers as per the Excel rules whenever you attempt to use a date in a context where a number is needed.

For example:

NOW() // is a date
NOW() + 1 // is a number

Maths operators assume that both values are numeric, so the result will be a number.

You can force the result back to a date by using the D() (Type Conversion Functions) function:

NOW() // is a date
D(NOW() + 1) // is a date, 1 day from now

Warning: When passing to a Filter expression, be sure that you are passing an actual DateTime value. A number used in a comparison with a DateTime field will not give you a useful result.

Related Articles