Add a special mainframe format for dates with century

[yruslan]

Background

Some mainframes represent dates as 7-digit numbers. Format is cyymmdd where the “c” is either a 0 or a 1. And 0 means century 19, 1 means century 20.

So:

• value 0980123 is date 1998-01-23
• value 1140123 is date 2014-01-23

But, since dates are stored as integers (they use PIC 9(7), not PIC X(7)), this means that dates in the 1900s will actually be stored & extracted as a 6-digit number (since a leading zero would be dropped from a numeric column.)

So, to "reverse" the above:
1998-01-23 would actually come through in an unload as the numeric value "980123" (a 6-digit number)

Further - if, for some bizarre reason, we had a date of 1906-01-23, it would come through as a 5-digit number (60123). We'd probably never encounter this, but just to check the processing logic.

And: for century 21 the “c” would be a 2, for century 22 the “c” would be a 3, etc

Feature

Add a special mainframe format for dates with century.

Example [Optional]

• value 0980123 is date 1998-01-23
• value 980123 is date 1998-01-23
• value 1140123 is date 2014-01-23
• value 60123 is date 1906-01-23

Proposed Solution [Optional]

Maybe to solve this very non-standard format, we can create a special pattern name, similar to epochmilli and use an UDF to for the conversion logic.

Proposed format name: mainframe_with_century (meaning 'mainframe with a century digit')

The logic:

1. Convert the number to string (60123 -> "60123").
2. Pad with 0s to make it 7 digit ("60123" -> "0060123").
3. Use the first digit to determine the century ("0060123" -> 0, "060123", Century 0 is 1900)
4. Use the rest of the string to determine the year, month and day within the century ("060123" -> year=6, month=1, day=23). Result: Date(1900 + 6, 1, 23) = 1906-01-23.