7 Replies Latest reply on Feb 12, 2015 7:48 AM by Narasimhulu M

    Date Time displayed as an Integer

      Share This:

      All

      When referencing a date  or date/time field in dashboards the information is rendered as an integer. In the database the field is an integer so this makes sense. However, I need to display the date, date/time fields as data and time. Is there a way that I can change the way the information is rendered with out having to go to the source forms and creating fields and workflow?

       

      Many thanks

      Pete

        • 1. Re: Date Time displayed as an Integer
          Jim Wilson

          You can apply functions to the appropriate column in your DataVew definition.  Details can be found in the online help under the fheading "Selecting a function"

           

           

           

          DATE

           

          Applies to numeric columns; converts an integer from an EPOCH date value to a calendar date format, for example, 8/15/09.

           

           

           

          DATE AND TIME

           

           

          Applies to numeric columns; converts an integer from an EPOCH date value to a calendar date and time format, for example, 8/15/09 9:25 am.

           

           

           

          DAY_OF_WEEK

           

          Applies to numeric or non-numeric columns; converts a value in a column of datatype integer, double, date, or timestamp to a day of the week value (Monday through Sunday).

           

           

           

          MONTH

           

           

          Applies to numeric or non-numeric columns; converts a value in a column of datatype integer, double, date, or timestamp to a month value (January through December).

           

           

           

          YEAR

           

           

          Applies to numeric or non-numeric columns; converts a value in a column of datatype integer, double, date, or timestamp to a year value, for example, 2009.

           

          BMC Software strongly suggests that you filter, aggregate, and transform your data before using it with the Pod Builder tool. If you attempt to apply functions to a large volume of data using the Pod Builder tool, the performance of your custom pods will be negatively impacted.

           

          If you have BMC Analytics for BSM installed?  If so, you can use the function fn_adjuested_date() to format the date into a user readable format when performing the data preparation.

          • 2. Date Time displayed as an Integer
            Jim Wilson

            Hi Pete,

             

            Did you get the information that you needed?  To help all the community, if you think that the response was helpful or correct, could you mark it accordingly and share any further information related to this thread.

             

            We all learn by helping each other :-)

             

            Thanks & Regards,

            Jim

            • 3. Re: Date Time displayed as an Integer
              Young So

              You can use a formula for excel to convert the dates.  Here is the formula.

               

              =A$ / 86400 + 25569

              You'll need format the column to show dates or it will show another number.

              • 4. Re: Date Time displayed as an Integer

                Hi Jim,

                 

                Will this work for timestamps inside diary fields ?

                I have a requirement to convert the epoch values in a diary field and convert it to readable values while rendering it thru BO application

                 

                Regards,

                Kiran

                • 5. Re: Date Time displayed as an Integer

                  Hi Jim Wilson,

                   

                  BMC recommends that you not use the FN_ADJUSTED_DATE stored procedure to prepare your data. Using this stored procedure will result in data incorrectly formatted for the BMC Dashboards for BSM product.

                   

                  For that you have to right custom view and then use it into you pod builder.

                   

                  Thanks & Regard,

                  Vikrant Chandele

                  Vyom Labs

                  • 6. Re: Date Time displayed as an Integer

                    please refer this link.It contain how to convert numeric date format in date and time format. It may be helpful for solving your date-time issue-

                     

                    http://docs.oracle.com/cd/E23943_01/bi.1111/e10544/format.htm

                    • 7. Re: Date Time displayed as an Integer

                      Hello Jim wilson/ Vikrant,

                       

                      yes your right wilson ..but also as Vikrant Chandele said BMC recommends that you not use the FN_ADJUSTED_DATE stored procedure to prepare your data.Using this stored procedure will result in data incorrectly formatted for the BMC Dashboards for BSM product.

                      so here my question is while creating custom view  if we give custom date format in your dashboard report  if it will work or not ...???

                      it its work let me know please .

                       

                       

                      Regards

                      Narasimhulu M