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

SAP Knowledge Base Article - Public

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 BusinessViews Manager , How To

Product

SAP Crystal Reports XI R2