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 ( | ). 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 the 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 );
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”