Date and Time Functions in TM1 10.2

There are three Date and Time formatting functions for TM1 10.2. Let’s take a closer look at these new functions.

If you have had to deal with date changes in a Turbo Integrator process you would know the pain of writing a lot of code for parsing dates and time using IF, SUBST, SCAN and pipes ( | ). And afterwards this would need to be converted back to strings or something else. Painful!

There are three new functions that can assist with this. They are:

  • FormatDate ( Date, <Pattern>, <Index> );
  • ParseDate ( DateString, <Pattern>, <Index> ) ;
  • NewDateFormatter (Locale, <TimeZone>, <UseUNIXTime>, <FormatterStyle>, <FormatterType>, <TimeType> );

FormatDate and ParseDate

These first two work absolutely fantastically in pair, use one to convert a favourite Excel style date (like “30/07/2014”) to number representing the date (like “19934”). The following TI script illustrates how this works. Note the outcome after the code where we show how the date is converted to a number and then used to create a date again.

sInputString = '30/07/2014';
nDate = ParseDate ( sInputString, 'dd/MM/yyyy',0 );
sOutputString = FormatDate ( nDate, 'MMM+yyyy', 0 );
ASCIIOUTPUT ( 'date.txt',sInputString, NumberToString ( nDate ), sOutputString );

Output: “30/07/2014”, “19934”, “Jul+2014”.

In the parameters nDate is a number, sInputString is a string, the pattern (‘dd/MM/yyyy’) is a normal string pattern, Index is a NewDateFormatter index that starts with 0.

Additionally, note that if all dates are in one format, there is no need to explicitly specify a format for every single one of them:

sInputString = '30/07/2014';
NewDateFormatter ( 'en_au', 'GB', 'serial', 'medium', 'date', '' );
nDate = ParseDate ( sInputString );
sOutputString = FormatDate ( nDate );
ASCIIOUTPUT ( 'date.txt',sInputString, NumberToString ( nDate ) , sOutputString );

Output: “30/07/2014″,”19933.958333333″,”30/07/2014”.

Time Zone Conversions

For this example the best possible option is “TM1 log time” that is logged in the standard time zone by default (UTC, GMT or London Time – whatever you prefer to call it).

Input is specified in the standard time (as always, then it is converted to non-standard zone).

sInputString = '2014-07-10 02:19:03.791' ;
nDate = ParseDate ( sInputString, 'yyyy-MM-dd h:m:s.S' ) ;
NewDateFormatter ( 'en_au', 'Australia/Sydney', 'serial', 'full', 'datetime', '' ) ;
sOutputString = FormatDate ( nDate, 0 ) ;
ASCIIOUTPUT ( 'date.txt',sInputString, NumberToString ( nDate ) ,sOutputString ) ;

Output: “2014-07-10 02:19:03.791″,”19914.096571655″,”Thursday, 10 July 2014 12:19:03 PM Australian Eastern Standard Time”

If you like this post, please spread the love…
This entry was posted in TI Functions, TM1 10.2 and tagged FormatDate, NewDateFormatter, ParseDate, Time, Turbo Integrator. Bookmark the permalink.