4 Replies Latest reply: Jun 5, 2013 11:13 AM by 1012919 RSS

    Date problem - formatted date is 1 day out

    sparrowDCLXVI
      Hi all

      I've got a problem when I format a date in BI Publisher. We're on EBS R12.0.4.

      The date looks like this in the XML output:

      <DATE_OF_BIRTH>1970-01-15T00:00:00.000+01:00</DATE_OF_BIRTH>

      In my RTF template, I use the following format:

      <?format-date:DATE_OF_BIRTH;’dd-MMM-yyyy’?>

      But the date then shows as 14-JAN-1970, instead of 15-JAN-1970.

      I think this is because the time zone of +01:00 is being misinterpreted but I'm not sure how.

      Any help appreciated.

      Thanks

      Alex

      Edited by: sparrowDCLXVI on Nov 3, 2009 11:03 AM
        • 1. Re: Date problem - formatted date is 1 day out
          user464310
          You will need to use the appropriate locale. In our case, we are in Qatar, hence we will use the following date format:

          <?format-date:DATE_OF_BIRTH;'YYYY-MM-DD';'Asia/Qatar'?>
          • 2. Re: Date problem - formatted date is 1 day out
            BIPuser
            Have a look at this document:
            http://blogs.oracle.com/xmlpublisher/2009/06/how_to_keep_your_dates_from_go.html

            Thanks!
            • 3. Re: Date problem - formatted date is 1 day out
              gnrao
              In the RTF template, do not use the Microsoft formatting mask for any of your "Date" fields.

              Please follow the steps below :

              1) In the RTF template, do not use the Microsoft format mask for the field,
              but use the Oracle abstract formatting mask instead.
              That is, under the Properties tab for that field, make sure the type
              is "Regular Text" and not "Date".

              2) In the Advanced tab, change the following (do this for each of the date
              fields) to use the Oracle formatting mask:

              <?format-date:DATE_OF_BIRTH;’dd-MMM-yyyy’?>
              To

              <?format-date:DATE_OF_BIRTH;’dd-MMM-yyyy’?;'UTC'?>

              3) Don't forget to include 'UTC', this is the main part of the workaround.
              The issue is that when XDO engine does not see a UTC offset in the date field
              in the XML data, it assumes the Date is in UTC/GMT timezone and so attempts to
              convert it to your timezone as specified under your personalization settings.
              This conversion can obviously affect the day. If you specify a UTC timezone in
              the format-date function, then it won't attempt a conversion.

              Thanks,
              GN
              • 4. Re: Date problem - formatted date is 1 day out
                1012919
                Hi All,

                I am also facing same issue. If i am runing report directly through publisher output is coming correct.

                But if I am runing through WSDL report out put is showing one day prior date(e.g. dob is 05-Jun-1970 in database, Same coming in xml but out put is showing 04-jun-1970)

                If any body is having any idea for this issue pls help me.

                Thanks
                Krishna