Date Formats

building reports desktopintelligence
1

Good morning,

I am using BO 4.1.3 and Oracle 7.1 database.

I have a number of predefined conditions that prompt users for dates, the data type is ‘D’ and if my user wants to run a report for say September 9, 1998 they input 9/4/98. I noticed yesterday that when I look at the SQL running on the database the date appears as 04/09/98. This works just fine but I’m wondering if anyone knows why the date reformats to dd/mm/yy vs. mm/dd/yy?

Thanks,

Julie

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

2

Since each DB can have different default date formats, BusObj resets the DB date format before each query. This information is stored in the PRM file.

Andrew J. Erthal
Orion Project Manager
Business Objects University

Chicago Office (847) 391-9898
St. Louis Office (314) 209-1994
St. Louis Fax (314) 209-7926

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

3

musgrava@NEWCASTLE2.SYNTEGRA.AGW.BT.CO.UK

It will depend on how the regional settings are defined on your system.

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

4

Hi Julie!

You need to change your object format into mm/dd/yy. I am giving a step by step procedure. just try to do that.

  1. Go to the Designer module and open your universe 2. Press your right mouse button on the date field. 3. Select the object format from the menu 4. Select date/time format
  2. Select the format mm/dd/yy (right side window) 6. Save your universe.

Now run your report. thats it. your report is ready. If it works, pls respond me. If not, i will give u another way to solve this problem.

Ravi.

______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

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

5

This may be nothing to do with BO as such, but may be something to do with the regional settings on the PC. For example, English (United Kingdom) gives you dd/mm/yy whilst English (United States) would give you mm/dd/yy.

Ginny.

______________________________ Reply Separator _______________________________
Author: Julie Moran Julie.Moran@FOREMOSTFARMS.COM at Internet
Date: 09/09/ 0 07:45

… This works just fine but I’m wondering if anyone knows why the date reformats to dd/mm/yy vs. mm/dd/yy?

Thanks,

Julie

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

6

musgrava@NEWCASTLE2.SYNTEGRA.AGW.BT.CO.UK MIME-Version: 1.0

My regional settings are United States English - Mm/dd/yy so that is not the problem.

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

7

We had the same problem. You need to change a couple of settings in the ociv7.sbo file. This file is located in the Oracle directory within your Business Obejcts directory. The default date formats in this file are set to dd/mm/yy.

Change the following settings in the [DEFAULTS] section:

InputDateFormat=‘MM/DD/YY’
InitDateFormat=‘MM/DD/YY’

This should reformat the dates to be in the mm/dd/yy format. Hope this helps.

Amy
amartel@allmerica.com
(508)855-3185
(508)852-3387 fax

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

8

Not the regional settings on your client – the regional settings on your Oracle Database server.

For example, the date format on our Oracle server is ‘DD-MON-YY’. The date format on my client PC is ‘mm/dd/yy’. So, if I enter a date condition value of 12/10/98, BO converts that to ‘10-DEC-98’ when generating the SQL. If I change my client date format to dd/mm/yy, when I enter 12/10/98 BO converts that to ‘12-OCT-98’.

Erich Hurst
Compaq Computer Corporation

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

9

Ravi,

Thanks for the response but I’m not actually using an object, I’m using the following predefined condition:

trunc(IC_TRAN_CMP_VW3.TRANS_DATE) <= @Prompt(‘End Date (MM/DD/YY)’,‘D’,MONO,FREE)

Then when I enter 9/4/98 and send the query to the database I have a tool called Precise where I can go and look at the performance of my query. Here is where I notice that the date has been reformatted to 04/09/98 but it does still get me the correct results. I’m just trying to figure out how and why this is done. My regional settings on my PC are English United States with a short date format of mm/dd/yy.

Andy mentioned earlier that it was stored in the PRM file. Where?

Thanks,

Julie

Hi Julie!

You need to change your object format into mm/dd/yy. I am giving a step by step procedure. just try to do that.

  1. Go to the Designer module and open your universe 2. Press your right mouse button on the date field. 3. Select the object format from the menu 4. Select date/time format
  2. Select the format mm/dd/yy (right side window) 6. Save your universe.

Now run your report. thats it. your report is ready. If it works, pls respond me. If not, i will give u another way to solve this problem.

Ravi.

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

10

Hi, Julie.

So everything is working fine, just not the way you envisioned it, huh?

Check the ociv7.sbo file in BusinessObjects\Oracle. There are two parameters for this Oracle-specific file: 1. InputDateFormat
2. InitDateFormat

Both should be the same, but they do different things. The InputDateFormat controls the format in which date objects in conditions are sent to the database. The InitDateFormat controls the date format set for the current session of your Oracle login. (It sends an ALTER SESSION command to set the desired date format).

The weird thing about your problem is that your input actually gets reversed. Normally, when the date format parameters in the *.sbo file are not set properly, you get Oracle errors about invalid month or the like.

Maybe your Precise tool is doing something. You may also want to run Tracker in the background to see the actual value that BusinessObjects is sending out. This may help you verify if the problem is with BO or your other application.

Good luck,
Luis Gonzalez

Thanks for the response but I’m not actually using an object, I’m using the following predefined condition:

trunc(IC_TRAN_CMP_VW3.TRANS_DATE) <= @Prompt(‘End Date (MM/DD/YY)’,‘D’,MONO,FREE)

Then when I enter 9/4/98 and send the query to the database I have a tool called Precise where I can go and look at the performance of my query. Here is where I notice that the date has been reformatted to 04/09/98 but it does still get me the correct results. I’m just trying to figure out how and why this is done. My regional settings on my PC are English United States with a short date format of mm/dd/yy.

Andy mentioned earlier that it was stored in the PRM file. Where?

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

11

X-cc: “Julie.Moran@FOREMOSTFARMS.COM” Julie.Moran@FOREMOSTFARMS.COM

InputDateFormat=‘DD-MM-YYYY HH:MM:SS’

This is a statement directly from C:\Program Files\BusinessObjects\Oracle\Ociv7.sbo

This how BO translates the date from Windows to Oracle. This format needs to be the same as the Oracle server date format. I know this is the default for BO and I believe this is the default for Oracle. So, the user enters 9/25/98, Oracle gets 25-09-1998.

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

12

Ah Yes, I think this is the solution!

I was confused yesterday because our DBA said that the date format on the oracle database is DD-MON-YY. When we use a conditional prompt iwth a format of ‘D’, we input 9/4/98 (Sept. 4) and when I look at the query running on the database the date is now 04-09-98 (Still Sept. 4 but in DD-MM-YYYY format). I couldn’t figure out where this was being translated inbetween BO and Oracle since we had no date formats on either end of this type.

Upon looking in the ociv7.sbo file at the InputDateFormat and InitDateFormat I found this:

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

I think then that this is how business objects translates the date to send to Oracle.

Thanks Luis!

Julie Moran
Foremost Farms, USA

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

13

When a user enters dates in response to a prompt, no data is retrieved for the second date if the data has a time stamp on it.

eg.

Work Date Between 01/11/2000 and 10/11/2000

No data is retrieved for 10/11/2000 because of the time stamp.

Please tell me how I can get around this. Will this require a change to the universe to drop of the time part of the object?

Lorinda Bailey

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

14

What RDBMS are you using? Some have a trunc function that removes the time portion.

George Baranowski
QuadraMed

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

15

This is a standard issue with all RDBMSs. When you do a between, you need to specify 1 day more than you want. Either that or you have to enter 10/11/2000 23:59:59.

When you enter a date with no time it assumes midnight, the beginning of the day.

The alternative is to build an object to trunc the time off. Then you can use that object as the one to prompt against.

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

16

I have an object set up in my universe as a date and I have the default format set as MM/DD/YY HH:mm so it would display like 7/14/01 13:29. Now when I use it in a report it displays fine. If I put it in a condition and enter a date say I enter 7/14/01. bo is changing it say 7/14/01 12:00:00 AM. Why is it using this format when I have said in the universe to use MM/DD/YY HH:mm. I have my regional setting set up this way and in my sbo file I made sure that both the input and audit date were set up the same way. Is there something else I’m missing?

Using BO 5.1.1, db sql server 7.0

Stacy Woodring
Programmer/Analyst
Performance, Inc
919-933-9113 x389

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

17

Stacy,
The format works only in the report after the data comes back from database.The SQL which goes to
database remains the same and the data type in BO is DATE. If you need to put this in condition then you may have to format the object using ‘convert’…

-Durgesh

I have an object set up in my universe as a date and I have the default format set as MM/DD/YY HH:mm so it would display like 7/14/01 13:29. Now when I use it in a report it displays fine. If I put it in a condition and enter a date say I enter 7/14/01. bo is changing it say 7/14/01 12:00:00 AM. Why is it using this format when I have said in the universe to use MM/DD/YY HH:mm. I have my regional setting set up this way and in my sbo file I made sure that both the input and audit date were set up the same way. Is there something else I’m missing?

Using BO 5.1.1, db sql server 7.0

Stacy Woodring
Programmer/Analyst
Performance, Inc
919-933-9113 x389

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

18

Stacy:
These queries in SQL server will help understand better:

  1. select getdate()
    the results : Jul 16 2001 4:21PM

  2. select convert(char(10),getdate(),101) 07/16/2001 ---- char format
    (101 - format style as mm/dd/yyyy)

  3. select convert(datetime, convert(char(11),getdate(),101)) Jul 16 2001 12:00AM — By default sql server puts 12:00 am as the time.

Remember if you have a date format July 16 2001 4:31 PM that you are querying then your query will fail . as the time stamp does not match with your time.

Hope it helps

Subrata
212.623.8272

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

19

Hi all,
One of the objects in my universe is ‘attendance time’. This field is saved as a number format. Examples are 2315, 915, 311. I need to change this into a date format in order to query on, for example, number of attendances between midnight and 2AM, 2AM to 6AM and 6AM to 8AM. How can I do this from within the business objects universe or otherwise. Any help would be very much appreciated. Thanks,
Fiona

BusObj 5.1
Oracle 7

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

20

to_date(to_char(Attendance_time,‘fm0000’),‘HH24MI’) should give you something to work with as a date. Otherwise if you only need buckets then the old decode with sign statment should work…

select
decode(sign(attendance_time-200),-1,‘12AM-2AM’,decode(sign(attendance_time-6 00),-1,‘2AM-6AM’,…))

Ken.

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