If you want to link external data providers to BO you will have to look into the date format and you will find out that there is not an easy way to do it for the cases you want to fully integrate the data. Let’s use the most common example of linking an SQL dump and transforming the date into a readable BO-format (Special thanks to Jörg Hofmann for the solution).
There are basically 2 ways to do that:
1. If you choose to edit the source file and then link it to BO, which at first sight may seem to be the easier way, you will have to do it every time you update the file. Already with the second file, the time for such manual procedures will increase exponentially.
2. Therefore invest some time into the date conversion directly in Business Objects.
Let’s take as an example one standard case: 12 Nov 2012 20:00:00
Create additional variable (Data – Variables – New Variable) with the name EntryDateString for example. In the formula field enter following: =ToDate(FormatDate(ToDate(<EntryDateString> , “dd mmm yyyy HH:mm:ss” ) ,”dd.mm.yyyy”) , “dd.mm.yyyy”)
This will transform the date to the dd.mm.yyyy format. Now if you want to have for example just the dd.mm you will need to go into the Format Cell (right click on the cell) and select Format – Date – dd.mm.