Date and Time Functions for Easy Date Manipulation

There were three Date and Time formatting functions released with TM1 10.2. These allow you to easily manipulate dates within a TI. 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 ( | ). Then, 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 the number representing the date (like “19934”). The following TI script illustrates how this works. Please note the outcome after the code. Here 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. This 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”