Hi,
if you can "predict" the possible formats, I think the way to go would be a partition on the date item, so that you accept all predictible formats. This has the great advantage of leaving the item as a date, thus avoiding having to convert from a string.
Another possibility would be to use a combination of MID, ISNUMBER and LEAVENUMBERS functions:
- If LEAVENUMBERS keeps the item as is, and the length is 8, the format is CCYYMMDD
- If LEAVENUMBERS keeps the item as is, this means it contains only numeric digits, and the length is 6, the format is YYMMDD (good luck if you also receive YYDDMM )
- if the character in 3rd position is not a number, it is the separator and teh format is MM?DD?CCYY
- if the 5th character is not a number, it is the separator.
I'd use a functional map for this, so that the "code" is reusable, and the ASFUNCTION function so that it can be used everywhere.
Hope that helps!
------------------------------
Laurent Barthelemy
Technical director
Satisco
mont saint guibert
+33 616792115
------------------------------
Original Message:
Sent: Tue May 12, 2020 07:49 AM
From: Santanu Baral
Subject: ITX--Date&Time format for multiple separator
Hi,
Thanks for your reply.
my challenge here is ,we are planning to create generic map for multiple partners.
So different partners can send date & time differently.
As of now,we have found CCYY-MM-DD,CCYY/MM/DD,CCYY.MM.DD ,CCYYMMDD,MM/DD/CCYY,MMDDCCYY
We can use multiple IF ELSE function with TODATETIME(Field1,"CCYY-MM-DD") to check all these date format
but is there any way to pass all these date combinations in a single TODATETIME function? or a date&time format which can check multiple separator
Like CCYY-M-D---using this format we can check single digit as well as double digit date,month
I have checked type tree customise option to get a date & time format for multiple separator but could not able to get it.
Initially i thought to use MEMBER function with TODATETIME function but it does not work
Thanks
Santanu
------------------------------
Santanu Baral
Original Message:
Sent: Tue May 12, 2020 02:44 AM
From: Karthikeyan RAGHURAMACHANDRAN
Subject: ITX--Date&Time format for multiple separator
Hi,
You could have your input filed as string/character and use the TODATETIME function to first recognize the input date format.
TODATETIME - function converts a text string of a specified format to a date-time item.
you could use this in combination with FROMDATETIME() to generate output in required format.
Knowledge center has more information on these functions.
https://www.ibm.com/support/knowledgecenter/en/SSVSD8_10.0.0/com.ibm.websphere.dtx.funcexp.doc/references/r_funcs_express_TODATETIME_date_time.htm
------------------------------
Karthikeyan RAGHURAMACHANDRAN
Original Message:
Sent: Tue May 05, 2020 02:09 PM
From: Santanu Baral
Subject: ITX--Date&Time format for multiple separator
Hi,
Is there any Date format is there which can accept multiple separator?
CCYY-MM-DD--so if in place of Hyphen if / or \ or . present in the input data then map should accept the data as valid date
using fromdatetime,i can convert this incoming date to expected output date format.
Please suggest if there is any format as such or any way to accept the date with different separator
Thanks
Santanu
------------------------------
Santanu Baral
------------------------------
#B2BIntegration
#SupplyChain