DATE Function in TM1: Syntax and Use

The DATE function in TM1 converts a serial date to a text version of the date in either YYYY-MM-DD or YY-MM-DD format.

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.

You might also like