The DATE function in TM1 converts a serial date (or numeric date) to a text version of the date in either YYYY-MM-DD or YY-MM-DD format. This is the opposite of the DayNo function, which converts from a string date to a numeric date
For example, let’s say you have a date of 9-Mar-98. The serial number representing this date (using the TM1 date system) is 13947. If you have a simple TM1 element (rather than a string element) with the value 13947 entered and have the element formatted to display a date (such as c:dd-mmm-yy), then the value will be displayed by TM1 as a date, rather than a value. It will still be stored as a value, but displayed as a date.
If you then want to store the date as a string, you need to use the DATE function.
Syntax of the DATE Function
The syntax is:
Date (SerialNumber, FourDigitYear); where:
- SerialNumber represents the numeric value stored (which could be displayed as a date (the 13947 above)
- FourDigitYear is an optional boolean value of either 0 or 1, where 0 tells TM1 to store a 2 digit year and 1 tells TM1 to store a 4 digit year. Also, if it is omitted, then it assumed to be a 2 digit year.
Example
Using the traditional TM1 date system (rather than the Excel serial date system):
Date (13947 , 1); will return the string ‘1998-03-09’
The Date Function can be then combined with other functions in TM1, like Month, to extract the month value from the string representation of a date.
Usage
Ensure when using the Date function that you are aware of the serial date system used by your TM1 model. There is a parameter in TM1s.cfg that can be set to force TM1 to use the Excel date system. In this instance, 1-Jan-1900 would be 1, whilst without it, 1-Jan-1960 would be 1. More information on this parameter can be found here.
This function can be used in both Rules and TI Processes.
