1612459 - Encrypted date in CMS database

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

BO Enterprise XI R3.3

SQL Server 2008

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

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)

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

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