1261295 - Date Stored as Numeric Data Type conversion to Date Type in Crystal Reports

Symptom

Numeric field stores date in format CCYYMMDD (century,year,month,day)
How to convert to Date type column?

Reproducing the Issue

Database stores dates in decimal field, 8 digits (CCYYMMDD)
Common with DB2 databases
How to convert to Date type in Crystal Reports?

Cause

Date of (Jan 31 1999) would be stored in DB2 numeric field as 20990131
Century is digit 1 and 2, second part of year is digit 3 and 4, month is digit 5 and 6, day is digit 7 and 8

Resolution

Create a formula in Crystal Reprots
Crystal Syntax Code below.......
Example uses Table1, column Date1, of data type numeric
Will convert the numeric value of 20990131 to the date Jan 31, 1999

WhileReadingRecords;
StringVar HoldYearPart1;
StringVar HoldYearPart2;
StringVar HoldMonth;
StringVar HoldDay;

//*********GET YEAR PART 1
//1. Convert to String
//2. Pick out the Century
//3. Change the Century to the year
//4. Convert the year back to string
//5. Place the year into the HoldYearPart1 variable

HoldYearPart1 := CSTR((ToNumber(Left(Cstr({Table1.Date1},0,""),2))-1),0);

//*********GET YEAR PART TWO
//6. Convert to String
//7. Pick out the Year(part two)
//8. Place the year into the HoldYearPart2 variable

HoldYearPart2 := Mid(Cstr({Table1.Date1},0,""),3,2);

//*********GET MONTH
//1. Convert to String
//2. Pick out the month
//3. Place the month into the HoldMonth variable

HoldMonth := Mid(Cstr({Table1.Date1},0,""),5,2);

//*********GET DAY
//1. Convert to String
//2. Pick out the month
//3. Place the month into the HoldMonth variable

HoldDay := Mid(Cstr({Table1.Date1},0,""),7,2);

//*********SHOW DATE BY CONCATENATING VALUES

Date(HoldYearPart1 & HoldYearPart2 & "-" & HoldMonth & "-" & HoldDay

Keywords

Date formatting, DB2 Date numeric, Numeric Date, Convert CCYYMMDD , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

SAP Crystal Reports XI R2