How to insert into date

ScottDun · Post by **ScottDun** » Thu Jun 02, 2016 11:52 am

Hi,

I am receiving a file and there is a Header that states PROVIDER20160602. I am to take out the date an insert it into a row in a table. I am using the function StringToDate( Link_INBND_TB.G_NYMMIS_HDR_DT,"%yyyy-%mm-%dd"). Now when a file says PROVIDER160602, without the 20, I am getting a datastage error bc it wants the 20. The exact error is APT_CombinedOperatorController(1),0: Data string '160513 ' does not match format '%yyyy-%mm-%dd': the value for tag %mm has fewer characters than expected.

So, how do I create a code saying that if there is no 20, to include it into the date?

Thanks

chulett · Post by **chulett** » Thu Jun 02, 2016 12:20 pm

Use %yy rather than %yyyy and you can't use the dashes in the format mask since they don't exist in your source string.

ScottDun · Post by **ScottDun** » Thu Jun 02, 2016 12:50 pm

So when I took the hyphens out and made it %yy, I will get an error and fail if the 20 is still in there. The testing scenarios are:

PROVIDER20160602 PROVIDER2016-06-02
PROVIDER160602 PROVIDER16-06-02
PROVIDER2016602 PROVIDER2016-6-02
PROVIDER2016062 PROVIDER2016-06-2
PROVIDER201662 PROVIDER2016-6-2
PROVIDER16602 PROVIDER16-6-02
PROVIDER16062 PROVIDER16-06-2
PROVIDER1662 PROVIDER16-6-2

The sender may send a file with any one of these. Is there one code/function for that or do I have to makeshift one?

Mike · Post by **Mike** » Thu Jun 02, 2016 6:30 pm

You seem to have a weak interface contract with your data provider.

If you want to be nice and handle all possible variations, you'll have to do a lot of extra work.

So what will you do with "16122"? January 22 or December 2?

Mike

ray.wurlod · Post by **ray.wurlod** » Thu Jun 02, 2016 9:39 pm

Andrew S Tanenbaum wrote:The great thing about standards is that there are so many to choose from.

See also https://xkcd.com/927/

chulett · Post by **chulett** » Fri Jun 03, 2016 4:12 pm

Heck, it just dawned on me that if you are going to use that DateFromComponents function then there's no need to worry about leading zeroes on the month and day since all function values need to be integers. All you'd need to do is add 2000 to the year if the century was missing.

D'oh.

UCDI · Post by **UCDI** » Mon Jun 06, 2016 8:24 am

Be sure all the dates are year 2000 if you do that. They were in the sample, but if you have stuff from 1985 or whatever.... sadly all I know to do in that scenario is if the value is 0-16 add 2000 else add 1900. This can still give incorrect results, of course. If your data has another field somewhere with a real timestamp you might be able to improve your educated guess logic. If you have future dated expire dates it gets even worse.

chulett · Post by **chulett** » Mon Jun 06, 2016 8:41 am

Of course. I was kind of in a hurry so didn't include all the caveats that you did. They need to be fully aware of the dates they could receive and - if needed - decide on a "century pivot" value so they know what to add if they all don't fall in the two thousands. And yes, those attempts can lead to... madness.

Too bad they don't have a more reliable / consistent source for this.

Teej · Post by **Teej** » Mon Jun 06, 2016 12:51 pm

chalet wrote:Use %yy rather than %yyyy and you can't use the dashes in the format mask since they don't exist in your source string.

Actually, that is not true. In Parallel Engine, we do not pay attention to the actual character in the separator itself. We just assume there's a separator there of some format.

So when you have "%YY%MM%DD" - we just assume there's no separator.

When you have "%YY...%MM...%DD" - we think the following examples are valid:

10---01---01
10...01...01
10***01***01

This have been a long standing behavior for the Date/Time/TimeStamp format.

-T.J.

chulett · Post by **chulett** » Mon Jun 06, 2016 1:39 pm

Really? That's completely new news to me. I seem to recall there being plenty of posts over the years where the errors posted using the StringToDate() function were all about using the delimiters incorrectly. Ah well. Certainly seems like something easily tested but I have no such capability.

Just for grins - you are using the three dots in your reply to represent any value used as a separator? This as opposed to specifically using three dots in some kind of Universe MATCHES equivalent. Yes?

Teej · Post by **Teej** » Mon Jun 06, 2016 2:54 pm

Correct. Dots, dashes, slashes, whitespace -- all get interpreted as "Something is there. Ignore it." Of course, there are certain reserved characters you can't use (i.e. %.)

There may be some certain restrictions that can be imposed, but I will have to review the codebase again to make absolutely sure.

-T.J.