5 Replies Latest reply on Jan 27, 2017 12:29 PM by Joe Oppelt

    Change date if in the future to beginning of the month

    Jared Weinstein

      I have some frustrating sales data with two separate date fields. This doesn't apply to all data, just a small percentage.

       

      Certain products come through with either 00000000 for a date in the one field and the end of the month in the other.

       

      So for example, I have a bunch of sales with a date of 1/31/2017 (with zeroes in the other date field).

       

      Since I don't have a better way to "place" these sales, I want to give them a date of the beginning of the month. Once the month is over, however, I am fine with the date reverting back to the end of the month.

       

      I tried the following:

      if [ZPOST_DATE]>=[Today] then date([Today]-(day([Today]-1)))

      ELSE

      [ZPOST_DATE]

      END

       

      Where [ZPOST_DATE] is the date field that is the last day of the month for these particular sales and [Today] is a parameter.

       

      I can't really explain why the above doesn't work. Almost all of my data gets lumped into nulls.

       

      The data source is an SAP HANA View so some date functionality does not work. I am on version 9.2.