SAP Knowledge Base Article - Public

1612459 - Encrypted date in CMS database

Symptom

Some date field values in CMS database are in the String (64) format and look like the following in the report:

BOXI R3 DBIT_ BOXIR3S_CMS_PRD CMS_InfoObjects6 LastModifyTime:
3230313120303620313720313520333420313420393533

Environment

BO Enterprise XI R3.3

SQL Server 2008

Cause

The date is encrypted, but could be decrypted using simple logic:

Original string:

3230313120303620313720313520333420313420393533

1. Replace "20" with a space and result string would be:

32303131 3036 3137 3135 3334 3134 393533

2. Drop  "3" on odd positions and result would be

2011 06 17 15 34 14 953

3. Use cDateTime function and the string above to convert it into a datetime value

Resolution

Use the following formula to decrypt:

stringvar Eyear:=mid({CMS_InfoObjects6.LastModifyTime},2 ,1)+mid({CMS_InfoObjects6.LastModifyTime},4,1)+mid({CMS_InfoObjects6.LastModifyTime},6,1)+mid({CMS_InfoObjects6.LastModifyTime},8 ,1);
stringvar emonth:=mid({CMS_InfoObjects6.LastModifyTime},12 ,1)+mid({CMS_InfoObjects6.LastModifyTime},14,1);
stringvar eday:=mid({CMS_InfoObjects6.LastModifyTime},18 ,1)+mid({CMS_InfoObjects6.LastModifyTime},20,1);
stringvar ehour:=mid({CMS_InfoObjects6.LastModifyTime},24 ,1)+mid({CMS_InfoObjects6.LastModifyTime},26,1);
stringvar eminute:=mid({CMS_InfoObjects6.LastModifyTime},30 ,1)+mid({CMS_InfoObjects6.LastModifyTime},32,1);
stringvar esecond:=mid({CMS_InfoObjects6.LastModifyTime},36 ,1)+mid({CMS_InfoObjects6.LastModifyTime},38,1);
stringvar elast:=mid({CMS_InfoObjects6.LastModifyTime},42 ,1)+mid({CMS_InfoObjects6.LastModifyTime},44,1)+mid({CMS_InfoObjects6.LastModifyTime},46,1);


CDateTime (Eyear+", " + emonth+", " + eday + " "+ehour +":"+eminute+":"+esecond)


Keywords

KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011, feature pack 03