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 site uses Akismet to reduce spam. Learn how your comment data is processed.