Paul,
What does this ding ding mean ???
Ashish
Listserv Archives (BOB member since 2002-06-25)
Well… BO says the Pivot year is 30…i.e … 29 is 2029 but 31 is 1931.
Soooo. My (Oracle) database field is a date that contains ‘01/01/0001’. When I pull the field in BO I see ‘01/01/2001’. Egad! It’s assuming the century.
So how do I see ‘01/01/0001’ instead?
Cindy Clayton - Business Objects Consultant AT&T
336.698.2144
Give people more than they expect and do it cheerfully. Talk slowly but think quickly.
Smile when picking up the phone. The caller will hear it in your voice. Mind your own business.
Learn the rules then break some.
Judge your success by what you had to give up in order to get it.
Listserv Archives (BOB member since 2002-06-25)
Hmmm… why does it say 0001? I guess we’ll save that for a different time… 
If what you want to bring into BO is exactly that, “01/01/0001”, you should be able to achieve that by telling the object that it’s a character instead of a date. Then, if you need to, you can convert it to a date in the report.
Hope that helps.
Paul
On Tuesday, April 18, 2000 11:40 AM, “Clayton, Cindy, HRSVC” [cindyclayton@ATT.COM] wrote:
Soooo. My (Oracle) database field is a date that contains ‘01/01/0001’. When I pull the field in BO I see ‘01/01/2001’. Egad! It’s assuming the century.
So how do I see ‘01/01/0001’ instead?
Listserv Archives (BOB member since 2002-06-25)
Have you checked that your regional settings on your PC include century?
Kathy Vazquez
Listserv Archives (BOB member since 2002-06-25)
Hmmm… why does it say 0001? I guess we’ll save that for a different time… 
Well…we’re converting an OOOLLLDDD system. They used that date to signify that the employee wasn’t to be included in a report. We’re converting ‘as is’ meaning just implement what is already there. The system manages employees who are labor union members. I’m starting to think that this project would have been easier if we had simply lobbied congress to do away with organized labor
If what you want to bring into BO is exactly that, “01/01/0001”, you should be able to achieve that by telling the object that it’s a character instead of a date. Then, if you need to, you can convert it to a date in the report.
Oh yuck! Hmmm. And I lose my time stamp right? And if I need to prompt the user to enter a start date and end date. In other words, are there any implications of treating a date as a char that I will inevitably stumble across?
Listserv Archives (BOB member since 2002-06-25)
Have you checked that your regional settings on your PC include century?
Yep…
I think it’s a genuine pivot year problem…
Listserv Archives (BOB member since 2002-06-25)
Ah, I understand what you’re saying. You might want to look into building some logic into your object that catches the occurence of a date value such as this. Maybe a decode statement can be written that will add the correct century field to the date before it returns the value to BO. I’m not an expert at doing that kind of thing, but I’m fairly certain it could be done.
Paul
On Tuesday, April 18, 2000 11:55 AM, “Clayton, Cindy, HRSVC” [cindyclayton@ATT.COM] wrote:
Oh yuck! Hmmm. And I lose my time stamp right? And if I need to prompt the user to enter a start date and end date. In other words, are there
any
implications of treating a date as a char that I will inevitably stumble across?
Listserv Archives (BOB member since 2002-06-25)
Hi, Cindy.
Someone had this issue about a year ago, and our very helpful ListServ friend “RM” had this to say:
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.
Cheers,
Luis Gonzalez
Listserv Archives (BOB member since 2002-06-25)
Cindy,
You are correct that this year value won’t appear correctly. The ‘earliest’ year that Business Objects will correct report against is:
January 1, 1901 at 0:00:00 through February 6, 2037 at 6:28:15.
That means that your data will automatically get ‘shifted’ by Business Objects to match its internal date storage requirements, even if it appears on your database correctly. (Remember that BusinessObjects stores the date as a number of seconds from a particular date).
As everyone is saying, you can convert your data to a Character string to retain the ‘Date’ portion (losing the time, unless you format the string to utilize it). It’s not very elegant, and you lose the proper sorting that occurs when dates are stored correctly.
You can decode the date to anotherunlikely date flag to lookout for (such as all ones) via:
decode( to_char (date_field,‘MMDDYYYY’), ‘01010001’, to_date (‘11/11/1111’,‘MM/DD/YYYY’), date_field)
You can also set your field to nulls, or even just filter OUT those dates if they were for users that you didn’t want to display.
(ie. to_char (date_field,‘MMDDYYYYY’) <> ‘01010001’ )
Another (more confusing) option would be to create two versions of your universe. One that has your object as a date field, and another that has it as a character field. Build your report on the Date_Version of your universe. Once the report is built, re-associate it to the Normal version of your universe.
Business Objects will not change the default Object type once the Microcube has been built. The data will be treated as a date after it is re-associated, although it is stored internally as a character.
Quirky, but it works.
-Robert
From: “Clayton, Cindy, HRSVC” cindyclayton@ATT.COM Soooo. My (Oracle) database field is a date that contains ‘01/01/0001’. When I pull the field in BO I see ‘01/01/2001’. Egad! It’s assuming the century.
So how do I see ‘01/01/0001’ instead?
Listserv Archives (BOB member since 2002-06-25)
In a message dated 00-04-14 16:39:39 EDT, you write:
On Friday, April 14, 2000 12:04 PM, “DRathbun@AOL.COM” [DRathbun@AOL.COM]
wrote:
As far as I know it is defined by Windows, not by BusObj. That is as far
as
how years are done on the client.
Bzzt! Try again.
Year functionality on your database server will depend on the server software instead.
True. Whatever BO gets in the query results is what it gets. This
question
pertains to when BO does date handling itself. And… different database servers do it differently, to a certain extent. That is, you can change
the
way they behave if you want to.
You can find the year under Win 98 and Win 95 (with the Y2K patch) on the Regional Settings under Control Panel.
I guess BO doesn’t pay attention to this setting (read on)…
Bzzzzt… (now it’s my turn)
BusObj DOES use this setting. I tested it.
I set the pivot year in Control Panel on my Win 98 system to run from 1900 to 1999. I also set my short date format to M/D/YYYY so as to be sure to see what rules BusObj followed. Next I started up 4.1.3 and entered a date query. If I entered 1/1/99 as a date, it showed up on the query panel as 1999. If I entered 1/1/00 as a date, it showed up as 1900.
Version 5.0.1 showed the same behavior.
After resetting my control panel settings to the default (1931-2030) the pivot date behavior in BusObj changed as well.
The answer to this specific question can be found in the Technical
Background section at the following URL:
SAP Software Solutions | Business Applications and Technology
As listed in this document, BusObj uses 2030 as the pivot year. It so happens that the pivot date that BusObj provides matches the default setting in Control Panel. But changing the setting in Control Panel DOES affect the behavior of BusObj.
So, what have I won?
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)