TM1 Forum

How to find out whether the incoming Date value is in MM/YYYY format or DD/MM/YYYY format in TI Process?

dwhcognos8 wrote:How to find out whether the incoming Date value is in MM/YYYY format or DD/MM/YYYY format in TI Process?

Since it would have to come in as a string, use the Long() rules function to check the length of it; the latter (minimum of 8 characters) will always be longer than the former (maximum 7 characters). Alternatively use the Scan() function to check for the presence of two / characters.

(And although you didn't ask for it I'll offer the suggestion that you make sure that the source system put leading zeroes if necessary on the date codes. It's less painful to parse the components of a date when it doesn't keep changing lengths.)

Alan Kirk wrote:(And although you didn't ask for it I'll offer the suggestion that you make sure that the source system put leading zeroes if necessary on the date codes. It's less painful to parse the components of a date when it doesn't keep changing lengths.)

Or you could use the the test with the Long function to modify the string yourself (assuming you don't have control over the format of the source but you do have control over the TI code)
E.g. to convert MM/YYYY to DD/MM/YYYY
vDate = IF( Long(vDate) = 7, '00/' | vDate, vDate);

You could get fancy with the FILL function but it doesn't seem warranted.

lotsaram wrote:

Alan Kirk wrote:(And although you didn't ask for it I'll offer the suggestion that you make sure that the source system put leading zeroes if necessary on the date codes. It's less painful to parse the components of a date when it doesn't keep changing lengths.)

Or you could use the the test with the Long function to modify the string yourself (assuming you don't have control over the format of the source but you do have control over the TI code)
E.g. to convert MM/YYYY to DD/MM/YYYY
vDate = IF( Long(vDate) = 7, '00/' | vDate, vDate);

You could get fancy with the FILL function but it doesn't seem warranted.

I'm not clear on what that gets them. 00/MM/YYYY obviously isn't a valid date. Also there's no guarantee that the short version will be 7 characters; if the field isn't formatted by the source with a leading zero, it's just as likely that it'll be 6 characters (3/2012) as 7 characters ( 11/2011). It's also not going to help if they have to parse the date into components and one row is (say) 12/3/2012 and the next is 4/12/2011. The code to pad these out into a uniform format or to split them based on the / character isn't complex but it's fiddly and potentially pointless if that can be attended to on the source side.

Or am I missing something?

Alan Kirk wrote:I'm not clear on what that gets them. 00/MM/YYYY obviously isn't a valid date. Also there's no guarantee that the short version will be 7 characters; if the field isn't formatted by the source with a leading zero, it's just as likely that it'll be 6 characters (3/2012) as 7 characters ( 11/2011). It's also not going to help if they have to parse the date into components and one row is (say) 12/3/2012 and the next is 4/12/2011. The code to pad these out into a uniform format or to split them based on the / character isn't complex but it's fiddly and potentially pointless if that can be attended to on the source side.

Or am I missing something?

Maybe I'm missing something. It was your suggestion to pad with leading zeros. I would have gone with using 01 for the particular issue of adding the "dd/" to a"mm/yyyy" format, for no other reason than it would at least give a valid date. If the month is also possibly m or mm then it all becomes (doable but) more complicated. Much better in any case to have a consistent date format with a consistent number of and position of characters.

lotsaram wrote:

Alan Kirk wrote:I'm not clear on what that gets them. 00/MM/YYYY obviously isn't a valid date. Also there's no guarantee that the short version will be 7 characters; if the field isn't formatted by the source with a leading zero, it's just as likely that it'll be 6 characters (3/2012) as 7 characters ( 11/2011). It's also not going to help if they have to parse the date into components and one row is (say) 12/3/2012 and the next is 4/12/2011. The code to pad these out into a uniform format or to split them based on the / character isn't complex but it's fiddly and potentially pointless if that can be attended to on the source side.

Or am I missing something?

Maybe I'm missing something. It was your suggestion to pad with leading zeros.

By which I meant that 3 January should always rendered in the source data as 03/01/2012, 12 May is always 12/05/2012, etcetera. In this way the date is always a consistent length, the month component (or day component for the US) is always in the same place, and the year component is always in the same place.

Similarly in the short version, the leading 0 is, if necessary, put in front of the month so that January to September are 01 to 09, with October to December being 10, 11 and 12, again yielding a length that is consistent.

Thanks for the Clarification Alan and Lotsaram.