Tip: Dealing with ASCII date "00000000" RRS feed

  • General discussion

  • Thought I would share this since it caused me so much grief.

    In some mainframe systems, some dates are stored as the string "00000000". In my SSIS package, I was trying to anticipate for this string, as well as any other combination of zeros (e.g., "000", "0000", etc), since I had already seen lots of dirty data in the flat file (like non-printing characters, etc).

    So, what I tried to do was perform an integer conversion on the string and test if it was the equivalent of the numerical value zero:

    Code Snippet

    (DT_STR)[ColumnName] == 0 ? ....

    Now, for some reason, that doesn't work, even though a similar operation in SQL does work:

    Code Snippet

    SELECT TOP 1 ISNUMERIC('00000000') FROM tableName

    In the end, I had to resort to testing for a match on the literal string "00000000" and hope that no other dates came in as "000" or other variation. Fortunately, this has been true so far.

    However, the moral of the story is, converting a series of zeros into a numeric zero, and testing against that, does not seem to work. I don't have a good explanation for why that is, but I would guess it has something to do with the limitation of the conversion function.

    Happy integrating.

    Saturday, April 5, 2008 4:04 AM

All replies

  • the following expression would work to test for the existence of "000", "0000", "00000", etc.:


    Code Snippet

    FINDSTRING((DT_STR)[ColumnName],"000",1) == 0 ?...


    Saturday, April 5, 2008 9:19 AM
  • Ah, good one Duane. Originally, I didn't know if I could expect to even see "00" so I went with a straight match. So far, I haven't seen anything other than "00000000" so I've been safe... but one never knows.


    (Also, one runs the real danger that a year 2000 date would evaulate to true and get flagged for handling, despite being a valid date.)


    Thanks for your methodl.


    Sunday, April 6, 2008 1:32 PM