Print Page - Covnert string date (yyymmdd) into a date (dd-mm-yyyy) as part of import

Title: Covnert string date (yyymmdd) into a date (dd-mm-yyyy) as part of import
Post by: dagland on 12 Apr 2015 11:12:57 PM

Hi All,

I am trying to import a data source into TM1 using performance Modeler, that has a date Number field (yyyymmdd) that I want to convert to a proper date for use as a date dimension. I have tried to use the following substring formulas but neither have worked:

vDate = Subst(The_Date_Number,7,2)|'/'|Subst(The_Date_Number,5,2)|'/'|Subst(The_Date_Number,1,4);
vDate = Subst(The_Date_Number,7,2)|'-'|Subst(The_Date_Number,5,2)|'-'|Subst(The_Date_Number,1,4);

I don't want to have to change the data source manually, prior to loading, so any suggestions would be appreciated.

Thanks
Danielle

Title: Re: Covnert string date (yyymmdd) into a date (dd-mm-yyyy) as part of import
Post by: japwah on 30 Dec 2015 09:12:23 AM

try converting the date number to a string first, then use it in your formula.

strDateNumber = numbertostring(The_Date_Number);

Title: Re: Covnert string date (yyymmdd) into a date (dd-mm-yyyy) as part of import
Post by: jimmy_k on 22 Feb 2016 07:23:07 AM

Never used before, but maybe this helps ...

http://users.skynet.be/fa436118/wim/tm1_newdateformatter_EN.htm (http://users.skynet.be/fa436118/wim/tm1_newdateformatter_EN.htm)

COGNOiSe.com - The IBM Cognos Community

Planning & Consolidation => TM1 => Topic started by: dagland on 12 Apr 2015 11:12:57 PM