Skip to Content

converting a date

Dear experts,

I have trouble converting a date.

There is a given field in WEBI called "date_now" and I would like to convert this date and write it into a new variable called "date_old".

The following canversions should be done:
- reduce the year by 1
- set the day to 01

For example:

date_now = "07.12.2016" ---> date_old = "01.12.2015"
date_now = "23.05.2016" ---> date_old = "01.05.2015"
date_now = "17.03.2014" ---> date_old = "01.03.2013"

It seemed quit easy in the beginning, but now I am struggling ...
Any help is appreciated!

TIA,
Henning

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Dec 07, 2016 at 01:14 PM

    what is your BO version?If it's BVI 4.1 sp06+ then you can follow below formula.

    =ToDate("01."+FormatDate(RelativeDate([date_now];-12;MonthPeriod);"MM.yyyy");"dd.MM.yyyy")

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 07, 2016 at 01:38 PM

    Hi Amit,

    thank you for your answer!

    I am working on "SAP BusinessObjects Business Intelligence 4.1 Support Pack 7".

    When I try to implement it on my side, I receive the following error:


    Any idea?

    BR,

    Henning

    Add comment
    10|10000 characters needed characters exceeded

    • what is the error message?

      what is datatype of "date_now" object? date or string?

      if it's string then try this.

      =ToDate("01."+FormatDate(RelativeDate(todate([date_now];"dd.MM.yyyy");-12;MonthPeriod);"MM.yyyy");"dd.MM.yyyy")

  • Dec 08, 2016 at 08:09 AM

    Hi Amit,

    thank you for this great way to convert the date!

    The problem was on my side as the "Locales and Time Zone" were set to German and I made a mistake when translating "MonthPeriod" into german.

    Have a great day,

    Henning

    Add comment
    10|10000 characters needed characters exceeded