How to display date from V2 OData service in a preferred format?

Question

I have couple of weird problems with the sap.m.DatePicker. As I always do, I searched for topics via google, they helped me, but not entirely.

What I want to achieve

I would like to have a sap.ui.table.Column in a sap.ui.table.Table and this column should have a template: new sap.m.DatePicker() with date formatted as dd.MM.yyyy.

What I have done so far

I am using a v2.ODataModel to populate a sap.ui.table.Table from an OData service. Everything went well until I decided to have the dates in the corresponding column displayed in a preferred format. At first, I was using only these properties, related to formatting in the DatePicker's constructor:

new DatePicker({
  displayFormat: "dd.MM.yyyy",
  valueFormat: "yyyy-MM-dd",
  value: "{" + fieldName + "}",
  //...,
})

In this way, all dates are displayed in the following format

Sun Aug 06 2017 03:00:00 GMT+0300 (FLE Daylight Time)

I remember yesterday reading in a documentation, that when I have binding, displayFormat will be ignored, which to me is ... I don't understand why is that.

Anyway, I found a solution, which gives the advice to set value like this:

value: {
  path: colName,
  type: new sap.ui.model.type.Date({
    pattern: "dd.MM.yyyy",
    //source: { pattern: "yyyy-MM-dd HH:mm:ss:fff" }
  })
}

At first, it seems to work well and the date is formatted and displayed correctly, but when I try to modify the date in a row... For example, I choose August 6th 2017. What is saved to the database via the OData service is 2017-06-08 00:00:00.000. This is not August 6th but June 8th. I select August 2nd - in the database, I see 2017-02-08 00:00:00.000 - February 8th. Seems like the month and day get switched. If I select, for example, August 30th, the change is not saved. As far as I understand what the problem is - there is no month with number 30, so it refuses to save it. Totally makes sense. :D

Then I tried to add/uncomment the line source: {}. Now for every row, the date column shows empty, as if every row in the database has null as value in that date column.

Next, I tried to remove displayFormat: "dd.MM.yyyy", valueFormat: "yyyy-MM-dd" from the definition of the DatePicker template - nothing changed. Interesting is - no matter if I remove those two lines or not, I can still change the date of a row (the change gets saved to the database), although month and day are still switched.

Then I came across another solution, which said, that I should add a formatter like this:

value: {
  path: colName,
  formatter: function(val) {
    return sap.ui.core.format.DateFormat.getDateInstance({
      pattern: "dd-MM-yyyy"
    }).format(val);
  }
}

This way, it all seems to work as it should. Now you may ask did I give up using the format dd.MM.yyyy. Answer is "No!", it just doesn't work.

So, this is the main question, that I want to ask: What's wrong with this formatting? No matter what other symbol I use, comma, dash, it always works (dd-MM-yyyy or dd,MM,yyyy). The dates are displayed with the corresponding symbol. When I try to use dd.MM.yyyy, it errors like that:
and like that:

Thank you all for your help! Will appreciate it!

Edit: I have just come up with the idea to workaround this issue. Unfortunately, it doesn't work and produces the exact same error, described above with pictures. Here is the nonworking solution:

return sap.ui.core.format.DateFormat.getDateInstance({
  pattern: "dd-MM-yyyy"
}).format(val).replace(/\-/g, '.');

https://stackoverflow.com/a/56954223 should solve the problem — Boghyon Hoffmann, Jul 15 '19 at 09:54

score 1 · Answer 1 · answered Aug 20 '17 at 10:22

1

Try using "dateValue" + "displayFormat" properties like here:

https://plnkr.co/edit/wkHv9s?p=preview

<DatePicker
    dateValue="{testModel>/myDate}"
    displayFormat="dd.MM.yyyy" />

answered Aug 20 '17 at 10:22

Andrew Naumovich

1,441
1
12
15

@user2177283 did it help you? – Andrew Naumovich Aug 22 '17 at 17:53
Thank you for your answer. I just tested it. At first, it seems to work. For all rows I can see the date in `dd.MM.yyyy`. When I select a new row, it displayes normally as well. But when I looked into the database, I saw sth interesting. I selected June 6th and it displays it. But what I saw in db is `2015-06-05 21:00:00.000`. Then I selected November 26th. In db I see `2015-11-25 22:00:00.000`. Our time zone is UTC + 2 and we use DST. I can clearly see, that it is related to that in some way. – user2177283 Oct 24 '17 at 06:31
Additionally, I have a datepicker outside of the grid as an external filter. When I filter by the selected date - by date greater than 10.10.2017, this is what I see in Fiddler: `GET localhost:port/ODataService.svc/Items/$count?$filter=(Itm_DateFrom%20ge%20datetime%272017-10-09T21%3a00%3a00%27%20)`. As far as I can see, it filters all with date greater than 09.10.2017 21:00 – user2177283 Oct 24 '17 at 06:41

Boghyon Hoffmann · Answer 2 · 2019-07-21T15:55:07.790

sap.m.DatePicker with date formatted as dd.MM.yyyy

I am using a v2.ODataModel.

In that case, bind value with the following options:

<DatePicker value="{
  path: 'ReleaseDate',
  type: 'sap.ui.model.odata.type.DateTime',
  formatOptions: {
    pattern: 'dd.MM.yyyy'
  },
  constraints: {
    displayFormat: 'Date'
  }
}"/>

^{(Assuming "ReleaseDate" is of type Edm.DateTime)}

Here is a demo you can run:

sap.ui.getCore().attachInit(() => sap.ui.require([
  "sap/ui/model/odata/v2/ODataModel",
  "sap/ui/core/mvc/XMLView",
], function(ODataModel, XMLView) {
  "use strict";

  const model = new ODataModel({
    serviceUrl: [
      "https://cors-anywhere.herokuapp.com/", // proxy
      "https://services.odata.org/V2/(S(SO45750998))/OData/OData.svc/",
    ].join(""),
    tokenHandling: false,
    defaultBindingMode: "TwoWay",
  });
  
  const viewDefinition = `<mvc:View xmlns:mvc="sap.ui.core.mvc" height="100%">
    <DatePicker id="dp" xmlns="sap.m" width="8.25rem" placeholder="Date"
      binding="{
        path: '/Products(0)',
        parameters: {
          select: 'ID, ReleaseDate'
        }
      }"
      value="{
        path: 'ReleaseDate',
        type: 'sap.ui.model.odata.type.DateTime',
        formatOptions: {
          pattern: 'dd.MM.yyyy'
        },
        constraints: {
          displayFormat: 'Date',
          nullable: false
        }
      }"
    />
  </mvc:View>`;

  const createView = () => XMLView.create({
    definition: viewDefinition,
    models: model,
    afterInit: function() {
      const fn = e => e.getSource().getBindingContext().getModel().submitChanges();
      this.byId("dp").attachChange(fn);
    },
  }).then(view => sap.ui.getCore().getMessageManager().registerObject(view.placeAt("content"), true));

  Promise.all([
    sap.ui.getCore().loadLibrary("sap.m", true),
    sap.ui.getCore().loadLibrary("sap.ui.unified", true),
  ]).then(createView);
}));

<script id="sap-ui-bootstrap" src="https://ui5.sap.com/resources/sap-ui-core.js"
  data-sap-ui-libs="sap.ui.core"
  data-sap-ui-theme="sap_fiori_3"
  data-sap-ui-async="true"
  data-sap-ui-compatversion="edge"
  data-sap-ui-xx-waitForTheme="init"
></script>
<body id="content" class="sapUiBody sapUiSizeCompact"></body>

As you can see in the network panel, the selected date is sent properly to the OData service without any issues. Just make sure to use the type sap.ui.model.odata.type.DateTime for fields that are of type Edm.DateTime.

For more information, see:

Documentation topic: Date and Time Related Controls: Data Binding
Related question: How to Add Date / Time from OData Service Correctly to UI?

How to display date from V2 OData service in a preferred format?

What I want to achieve

What I have done so far

2 Answers2