Originally posted by: mseedig

I have a map that was delivered to me, and in testing it I found that a rule was not working as I expected, and nothing I have tried is fixing the issue. The rule is trying to load Ansi X12 data to an Oracle database. The data can come in either as just a date or as a date/time string.

If the data has only a date, the data looks like this: DTP*435*D8*20140625
If the data has both a date and time, the data looks like this: DTP*435*DT*201406250314

The map rule is:
=TEXTTODATE(LOOKUP(DateTimePeriod Element:DTP Segment:In1, DateTimeQual'r Element:DTP Segment:In1 = "435"))

The data type on the column in the Oracle table is DATE. In the type tree, the Item Subclass is Date & Time,
Interpret as Character, Format {CCYY-MM-DD}[ {HH24:MM[:SS]}].

If the input data contains the date only data, then the date loads with a time of 00:00:00, which is great. However, if the input data contains both the date and time, I get (null) in the database. I have tried TODATETIME instead of TEXTTODATE, and I have tried specifying the mask format, but nothing I've tried has worked. I know this is an elementary task and it should have taken me a minute or two to correct, but I guess I'm so deep in the forest that I can't even tell what I'm looking at anymore. If anyone has any suggestions, I would be happy to hear them. Thank you.

Originally posted by: Arun Ramamurthy

Try something as below in your map rule:

=TODATETIME(LOOKUP(DateTimePeriod Element:DTP Segment:In1, DateTimeQual'r Element:DTP Segment:In1 = "435"),"{CCYYMMDDHH24MM[SS]}")

Originally posted by: mseedig

Thank you, Arun. I had tried very similar rules without luck. That one at least loaded the date and time when both were present, but (null) if only a date was present. I tweaked the rule until I came up with something that worked in either scenario. I will paste the two versions of the rule that ended up working for me here in case anyone in the future has a similar issue. Thanks again for your help. I'm still battling a rule where I can't get EXTRACT to work as I expect, but I'm not done trying!

=IF(SIZE(DateTimePeriod Element:DTP Segment:In1) > 8 &
 DateTimeQual'r Element:DTP Segment:In1 = "435",
                TODATETIME(LOOKUP(DateTimePeriod Element:DTP Segment:In1,
                DateTimeQual'r Element:DTP Segment:In1 = "435"),
                "{CCYYMMDDHH24MM[SS]}"),
        TEXTTODATE(LOOKUP(DateTimePeriod Element:DTP Segment:In1,
        DateTimeQual'r Element:DTP Segment:In1 = "435")))

=IF(DateTimeQual'r Element:DTP Segment:In1 = "435" &
 DateTimePeriodFormatQual'r Element:DTP Segment:In1 = "DT",
                TODATETIME(LOOKUP(DateTimePeriod Element:DTP Segment:In1,
                DateTimeQual'r Element:DTP Segment:In1 = "435"),
                "{CCYYMMDDHH24MM[SS]}"),
        IF (DateTimeQual'r Element:DTP Segment:In1 = "435" &
        DateTimePeriodFormatQual'r Element:DTP Segment:In1 = "D8",
                TEXTTODATE(LOOKUP(DateTimePeriod Element:DTP Segment:In1,
                DateTimeQual'r Element:DTP Segment:In1 = "435"))))