Hi There,
This is a common issue for anybody using 3rd party BI Tools. I have seen a few different solutions for Cognos Impromptu. One of them is the following formula:
If (Date – For G/L (and Voucher) <> 0 and Date-For G/L (and Voucher) <= 99999) then (datetime-to-date(days-from-1900-to-datetime(date-to-days-from-1900(datetime-to-date(make-datetime(string-to-integer((‘19’ + substring(number-to-string(Date-For G/L (and Voucher)),1,2))),01,01)))+string-to-integer(substring(number-to-string(Date-For G/L (and Voucher)),3,3))-1))) else if (Date-For G/L (and Voucher)>99999)then (datetime-to-date(days-from-1900-to-datetime(date-to-days-from-1900(datetime-to-date(make-datetime(string-to-integer((‘20’ + substring(number-to-string(Date-For G/L (and Voucher)),2,2))),01,01)))+string-to-integer(substring(number-to-string(Date-For G/L (and Voucher)),4,3))-1)))else NULL
Please Note: this will convert CYYDDD into dd/mm/yyyy and not into mm/dd/yyyy as you require, but I hope it will give you enough info nevertheless.
I have seen a shorter solution elsewhere, but do not have this info with me at the moment. Should you require an alternative I'll gladly provide it, but I expect you'll get a few more suggestions from other users.
Rgds, Sef
Sef van den Nieuwelaar
Australia
B732 on NT, XE on NT, B732/A73 on AS400, B733 on NT