Skip to Content
avatar image
Former Member

BO changes date to timestamp; DB2 database gives error

My company is working to migrate from BO3 to 4.1. I was given designer access for a universe which connects to a DB2 database so that I could get it ready for 4.1.

I'm having a problem where for all the date field prompts (there are hundreds of date fields), BO is turning the date values into timestamps, sending the query to the DB2 database, and the database is kicking back an error because it was expecting a date and not a timestamp. It makes no difference whether the user enters the date by typing the date or clicking on the calendar picker.

"The following database error occurred: [IBM][CLI Driver][DB2] SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007"

I'm assuming that there is a setting somewhere in BO, but I haven't been able to find it. My BO admin suggested working around it by casting all the date fields to timestamps, and although this would probably work, I have to think there must be a better way. This was working correctly BO3, after all.

EDIT (I can't figure out how to reply to anyone):

The version of BO we are migrating to is out of my control.

I don't have access to Knowledge Base articles. I don't have an account, and there doesn't appear to be any way to create one. I already spoke with a web chat person and explained this, and she referred me to this message board.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Jan 09 at 09:07 PM

    Hi Gerald,

    There are changes in the BI platform for date and date time objects. The previous versions only supported Date objects, you had to select one or the other. Whereas the new IDT provides DATE and DATETIME.

    The correct file depends on the driver you are using. When you test the connection in the Universe Design tool, and server responds click the details and it will give you the exact information. Multiple files may indicate foreign languages, so please take caution. Is your server date US? UK? etc.

    The first step is to test locally the date object format. Format always depends on what your database is expecting. Also does your database expect a timestamp? Check with your db what is the format there for date objects.

    This can be done by creating a few test date objects in the universe and editng the objects advanced tab single quotes 'yyyy-mm-dd' or

    USER_INPUT_DATE_FORMAT parameter value to the time stamp('yyyy-mm-dd HH:mm:ss')

    Once you know the correct format then you can edit the local copy of the prm file.

    <Parameter Name="USER_INPUT_DATE_FORMAT">{\d 'yyyy-mm-dd'}</Parameter>

    Make a backup of the file. Make the changes. Close the Universe design tool and then re-launch.

    You can test in the query panel if it works. Or you can also test in the Webi Rich Client.

    After it works locally, then you will need to take a copy of the changed prm file and copy it to each webi server. For the changes to occur at the server level, your Admin will need to do this and restart the SIA. Server intelligence Agent. The ADMIN will know what this is in the Central Configuration Manager. (CCM)

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank you Jacqueline. The correct file was db2udb.prm. I changed

      • <Parameter Name="USER_INPUT_DATE_FORMAT">'yyyy-mm-dd HH:mm:ss'</Parameter>

      to

      • <Parameter Name="USER_INPUT_DATE_FORMAT">'yyyy-mm-dd'</Parameter>

      Now all date fields are formatted correctly as 'yyyy-mm-dd' and the database doesn't throw an error when filtering on a date field (at least locally).

      I'm willing to call this good enough, but I'm interested that you said that both date and datetime are supported. My database has a mixture of both, and although the change to the prm file fixed the dates, it broke the datetimes. Is there a way to specify one format for dates and a different format for datetimes? If so, how do you specify which fields are which? In the Universe Designer object properties Definition tab, I only see options for data types Character, Date, Long text, and Number. Could this be because I'm on version 4.1 and not 4.2? I could probably work around this by entering the Database Format on the Advanced tab for all datetimes, but I don't think this is what you were referring to.

  • Jan 03 at 10:05 PM

    Look at this KB.

    2234950 - [IBM][CLI Driver][DB2] SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 06 at 02:51 AM

    Okay, my BO Admin sent me a PDF of the KB article, but much if it is unclear and/or inaccurate. It says:

    ______________

    Cause

    The error appears because of the difference in the SQL generation in XI 3.1 and BI 4.1. It is just MM/dd/yyyy in 3.1 query.

    Resolution

    Look at the db2.prm file in BI 4.1 and add parameter <Parameter Name="USER_INPUT_DATE_FORMAT">'format set at the database level (eg: mm/dd/yyyy)' </Parameter>

    NOTE : take a backup of the prm file before making any changes.

    Location of the prm file will be known from the test connection. (C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\db2\extensions\qt)

    ________________

    First of all, although BO 3.1 displayed the date in M/d/yyyy format in the GUI, the SQL code is in the format yyyy-MM-dd. Given the error being given, I would think the latter is what anything should be changed to.

    Next, the KB article says to change a program setting file. I don't have access to make changes in Program Files; I would have to get a system admin to do it for me, and I would need to know exactly what needs to be done... however...

    The KB article says to open db2.prm. This file doesn't exist. I have three files named: db2iseries.prm, db2mvs.prm, and db2udb.prm. The article says to add a USER_INPUT_DATE_FORMAT parameter, but all three files already have this parameter, with each file having a different value set.

    Is the KB article saying that the parameter should literally be set to 'format set at the database level (eg: mm/dd/yyyy)'? Or does it mean mm/dd/yyyy? Does it matter that earlier the article said that mm should be MM? And again, shouldn't it be yyyy-mm-dd? Whatever it is, should it be in single quotes or not?

    I don't even understand why a program setting file would make a difference, so I tried adding the parameter to the universe's parameters, but all the different possible values I tried seemed to have no impact in how BO was handling dates.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 04 at 11:28 AM

    I'm not able to view knowledge base articles.

    Add comment
    10|10000 characters needed characters exceeded

    • Here is the resolution in the KB article.

      Cause

      The error appears because of the difference in the SQL generation in XI 3.1 and BI 4.1.
      It is just MM/dd/yyyy in 3.1 query.

      Resolution

      Look at the db2.prm file in BI 4.1 and add parameter <Parameter Name="USER_INPUT_DATE_FORMAT">'format set at the database level (eg: mm/dd/yyyy)' </Parameter>

      NOTE : take a backup of the prm file before making any changes.
      Location of the prm file will be known from the test connection. (C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\db2\extensions\qt)

      For cluster environment , make changes in both the servers.
      Restart the SIA.

  • avatar image
    Former Member
    Jan 07 at 05:51 AM

    My previous reply didn't show up, so I will try again.

    The KB article says to make changes in Program Files, but my company doesn't allow me to do this. I would have to reach out to a system admin to make the change, so I would need to know exactly what I'm asking to be done.

    Inside the specified folder, I have three .prm files, but none of them are named db2.prm. All of them already have a USER_INPUT_DATE_FORMAT parameter, but the values are different in each file.

    Precicely what is the value supposed to be? Is it literally supposed to say 'format set at the database level (eg: mm/dd/yyyy)'? Should it say 'MM/dd/yyyy", as it was supposedly set to in 3.1? Should it be mm/dd/yyyy as in the example? Should it be 'yyyy-mm-dd', the format actually expected by the DB2 database? Should the single quotes be included?

    Since I have access, I tried adding this parameter to the universe parameters with various values, but none of them seemed to have any impact.

    I don't know what a cluster environment or SIA is or how to restart it. (I am a universe designer, not a server admin.)

    Add comment
    10|10000 characters needed characters exceeded