Questions

CYYMMDD format in DB2

+
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.