Date Converion

building reports desktopintelligence
1

We are using Oracle 7.3 and are having trouble retrieving certain dates
through Business Objects 4.1.3. There are some dates set to a default of
01/01/0001 in one of our tables. We cannot retrieve these dates correctly
with Business Objects. If we enter the criteria DATE = 01/01/0001,
Business Objects converts it to 01/01/2001 when we hit enter. If we enter
the criteria DATE < 1930 (that is just an arbitrary date, any date will
do), the data is retrieved, but the date is displayed incorrectly as
01/01/2001. We are having the same problem when using Access to retrieve
the data, so it is probably a Windows date format/conversion issue. If we
run the query in SQL Worksheet, we get the correct results. Is there a way
to make Business Objects/Windows recognize that 0001 is a valid year? Any
help with this issue will be greatly appreciated!

Here is an example of the results:
Business Objects:
Policy Number Date
3903000 01/01/2001
19034200 01/01/2001
19592300 01/01/2001

SQL Worksheet:
Policy Number Date
3903000 01/01/0001
19034200 01/01/0001
19592300 01/01/0001

Thanks,

Lynne Fee
General American Life Insurance
St. Louis, MO

Listserv Archives (BOB member since 2002-06-25)

2

We are using Oracle 7.3 and are having trouble retrieving certain dates
through Business Objects 4.1.3. There are some dates set to a default of
01/01/0001 in one of our tables. We cannot retrieve these dates correctly
with Business Objects. If we enter the criteria DATE = 01/01/0001,
Business Objects converts it to 01/01/2001 when we hit enter.

=========================================================================

Hi Lynne,

You should have a look at your ociv7.sbo file in the BusinessObjects\Oracle
Directory.
Check out for the date formats there …

InputDateFormat=‘DD-MM-YYYY HH:MM:SS’
InitDateFormat=‘DD-MM-YYYY HH24:MI:SS’

Probably you have entered a two digits year there …

Hope I could help you

Cheers

                            Martin Lidl
                            Consultant
                            KPN-Orange
                   e-mail: martin.lidl@orange.be
                "Normal is the average of extremes"

Listserv Archives (BOB member since 2002-06-25)

3

Lynne,

Unfortunately, you are not going to have much luck returning the Object as a
Date format.

You can, however, convert the date to a String format and it will appear
correctly in your report, but you won’t be able to use any of the date
functions against it.

The EARLIEST date that you can use when running against Business Objects is
with the Year 0100. I would suggest changing the data in your database to
another date that stands out as well, and is less likely to pose future Y2K
issues. For the sake of argument, I use:11/11/1111

It stands out like a sore thumb as well as 01/01/0001 and it took very
little modification to get our ‘autopopulate’ to substitute this value. In
addition, it only took a single ‘UPDATE’ to convert all of the dates.
Better still, BO has no issues reporting against this date.

You can get BO to report 01/01/0001 as a DATE object, but it is by no
means easy to do, nor intuitive. I would suggest it only as a last resort.

Thanks!

-rm

Listserv Archives (BOB member since 2002-06-25)