Questions

CYYMMDD format in DB2

Tags:
+
0 Votes
Locked

CYYMMDD format in DB2

pravins.s
I have a date format in db2 which has a date in
the below format. CYYMMDD in which the C stands
for century, YY for year, MM for month and DD
for day. Right now am storing it as string. for
example, 1082702. How to convert this string to
date? I want to find the diference between the
dates which are in the above format. Please
help.
  • +
    0 Votes
    OldER Mycroft

    So unless you alter the sequence you input your data, it'll never work.

    Also, as far as I'm aware - this ain't the 1st Century. Having a single digit is probably confusing for you.

    +
    0 Votes
    Ed Woychowsky

    Over a decade ago I worked at an insurance company that did this. The way it worked was that the C stood for the century that the company was founded, for example, 1=1800, 2=1900 and 3=2000. As for how to convert it, if you can convert it to a string then use a substring function to breaking it into individual parts that should do most of it. The final part is converting the century code.

    Hope this helps.

    +
    0 Votes
    OldER Mycroft

    I notice you made the Freudian slip of the ageing among us - you didn't say "Last Century I worked at ..."

    Mind you, I probably would've done exactly the same.

    +
    0 Votes
    Ed Woychowsky

    I'm old. Here's a hint as to how old, I saw Star Trek The Motion Picture on my 18th birthday. Alas, it was something of a let down.

    +
    0 Votes
    Jonathan.Davis

    CYYMMDD is an old IBM Date Format.
    When C = 0 the Century is 1900
    C = 1 the Century is 2000.

    An easy way to handle this format is below

    INPUTDATE + 19000000

    INPUTDATE will now be in CCYYMMDD format.

    Sometimes it takes an old programmer to teach new kids tricks.

  • +
    0 Votes
    OldER Mycroft

    So unless you alter the sequence you input your data, it'll never work.

    Also, as far as I'm aware - this ain't the 1st Century. Having a single digit is probably confusing for you.

    +
    0 Votes
    Ed Woychowsky

    Over a decade ago I worked at an insurance company that did this. The way it worked was that the C stood for the century that the company was founded, for example, 1=1800, 2=1900 and 3=2000. As for how to convert it, if you can convert it to a string then use a substring function to breaking it into individual parts that should do most of it. The final part is converting the century code.

    Hope this helps.

    +
    0 Votes
    OldER Mycroft

    I notice you made the Freudian slip of the ageing among us - you didn't say "Last Century I worked at ..."

    Mind you, I probably would've done exactly the same.

    +
    0 Votes
    Ed Woychowsky

    I'm old. Here's a hint as to how old, I saw Star Trek The Motion Picture on my 18th birthday. Alas, it was something of a let down.

    +
    0 Votes
    Jonathan.Davis

    CYYMMDD is an old IBM Date Format.
    When C = 0 the Century is 1900
    C = 1 the Century is 2000.

    An easy way to handle this format is below

    INPUTDATE + 19000000

    INPUTDATE will now be in CCYYMMDD format.

    Sometimes it takes an old programmer to teach new kids tricks.