Date Format

system · November 7, 1998, 1:21am

Hi,

We are facing a problem of making dates consistent throughout our universe. We want to use date objects in conditions and pass them date format as ‘dd-mm-yyyy’. However, BO picks up default regional settings of the computer. On some computers the default setting is ‘mm/dd/yy’ and on others it is ‘dd/mm/yy’ and so on. How can we force BO to ignore regional setting and just use one format for all dates? We tried to force it by setting up date format on ‘Advanced’ tab under ‘Database Format’ section but it is not working. Is there anyway we can enforce this?

Any help will be greatly appreciated.

neel

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

system · November 7, 1998, 6:06pm

One way might be to define your objects as Character data type instead of Date. Then BO won’t apply regional settings to the user input.

Another way to do it might be to use the date format ‘dd-mon-yyyy’ instead of ‘mm’. That way, users have to spell out the month, ‘jan’, ‘feb’, etc. Then your USERS won’t get confused about which is the day part and which is the month part – which is more important than the DATABASE getting confused about it.

If you use ReportScript, I suspect you could check the format of user-entered dates before running the queries.

By the way, I’ve found that BO accepts a myriad of date formats for user input – “11/7/98”, “7-nov-98”, even “November 7, 1998” and “7 November 1998”. Is the regional format only applied when the day and month are both entered as numbers?

–Erich

Hi,

We are facing a problem of making dates consistent throughout our universe.
We want to use date objects in conditions and pass them date format as ‘dd-mm-yyyy’. However, BO picks up default regional settings of the computer. On some computers the default setting is ‘mm/dd/yy’ and on others
it is ‘dd/mm/yy’ and so on. How can we force BO to ignore regional setting and just use one format for all dates? We tried to force it by setting up date format on ‘Advanced’ tab under ‘Database Format’ section but it is not
working. Is there anyway we can enforce this?

Any help will be greatly appreciated.

neel

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

system · May 11, 2000, 12:27pm

Can I change the format of a date on a report from julian to mm/dd/yy? For example, the date displayed is “64018” and I would like it to be “01/18/64”.

Thanks,
Dave

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

system · May 11, 2000, 1:08pm

Right-click in report column, select “Format Cell…”, click Number tab, then select Date/Time category and mm/dd/yy format. Note that if the Julian date has a fractional component (i.e., the date has a timestamp) and the format selected is mm/dd/yyyy HH:mm:ss, the time component is converted to 00:00:00. I am investigating this.

Can I change the format of a date on a report from julian to mm/dd/yy? For example, the date displayed is “64018” and I would like it to be “01/18/64”

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

system · October 23, 2000, 7:36pm

I have an Oracle database that has dates formatted in a VARCHAR2(8) in a YYYYMMDD display format. I have attempted to change the format in the universe by using the
to_date(,) function. I receive an error “The expression type is not compatible the Object type”. Can any one offer suggestions

Thank You

Bill McLamara
Wk. Phone 616.247.2463
Fax 616.248.7066
WMCLAMAR@STEELCASE.COM

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

system · October 23, 2000, 8:00pm

Good to see a post from you. Does your object have a type of Date? If it still is character, this error can come up.

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

system · October 23, 2000, 8:05pm

In a message dated 00-10-23 15:54:31 EDT, you write:

I have an Oracle database that has dates formatted in a VARCHAR2(8) in a
YYYYMMDD display format. I have attempted to change the format in the universe by using the
to_date(,) function. I receive an error “The expression type is not compatible the Object type”. Can any one offer suggestions

By default the object type is Character. You will need to change the type to Date since you are converting from character to date with your function.

Incidentally, this error will NOT prevent you from using the object. The object type set in designer is simply used to determine the default formatting on the report, as well as some validation when used in a query prompt.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

system · October 24, 2000, 2:49am

Chris@LEGALWEBNET.COM

Bill,

Are you able to change your NLS_Date_format to something you want to use? The NLS alters the format of the date stored at the oracle database level. The to_date(,) function works on a character string not a date string which is why your getting that error. You might want to use the to_char(,) string which will convert a date to a character string and format it. Or you can use the FormatDate(,) function which will also convert the date to a character string.

Hope this helps

Chris

Mosaic Consulting LLC
Managing Consultant
Chris@LegalWebNet.com

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

system · October 25, 2000, 5:42pm

X-cc: jhertel@businessobjects.com

Chris, Dave and SKrandel,
Thank you for your reply to my date format issues. Let me give you a few more details. In Oracle the reason the date fields were set to VARCHAR2 (8) was to be able to display a pay period e.g. W199901 W(week) 1999 (year) 01(period week 1) or M199901 M (month) 1999 (year) 01 (pay period month 1). This way they could use alpha numeric values. They have since changed the values with in the column to display as a format YYYYMMDD but the field time in Oracle is still VARCHAR2 (8). I have tried to change the object type in BO Univ. to a date type. Thats when I received the error “The expression type is not compatible the Object type”. I have also tried to create my own object and make it a date type. In both the original object and in the object I created I have tried to use the to_Char(,), to_date(,) and the FormatDate),) functions. I am still unable to change the format. We are currently still running on BOA 4.1.4. I don’t know if that makes a difference. Do you have any other Suggestions?

Thank You

Bill McLamara
Wk. Phone 616.247.2463
Fax 616.248.7066
WMCLAMAR@STEELCASE.COM

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

system · November 30, 2000, 4:12pm

All;
I am currently trying to change a text date object, 20000103 (########), to MM/DD/YY.
Any insight is greatly appreciated.

Oracle 7.3
BO 5.1.1

Jeff

_______________________________________________________ Tired of slow Internet? Get @Home Broadband Internet http://www.home.com/xinbox/signup.html

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

system · November 30, 2000, 4:41pm

Convert it to character using the char function. Then use the todate function to convert it to a date. This will do it in BO. I’m sure there is a way to do it in Oracle, but I’ll leave that for Dave.

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

system · November 30, 2000, 6:30pm

In a message dated 00-11-30 11:41:09 EST, you write:

Convert it to character using the char function. Then use the todate
function to convert it to a date. This will do it in BO. I’m sure there
is
a way to do it in Oracle, but I’ll leave that for Dave.

I am currently trying to change a text date object, 20000103 (########), to MM/DD/YY.

Okay Steve, I’ll take it from here…

You have a character string with the format YYYYMMDD. You may want to convert it to a “real” date. For that, all you have to do is use the to_date() function, as follows:

to_date(my_string,‘YYYYMMDD’)

The syntax of to_date() in Oracle is to_date( date_value, string_format ). You enter the format that tells the to_date() function how the date looks, and it converts to a true date value. You can format it any way you want to on the report side, including what you are asking for. You can sort by date, query by date, etc.

Now, if you want to return the value back to a character string in the format you supplied, that requires one more step:

to_char(to_date(my_string,‘YYYYMMDD’), ‘MM/DD/YY’)

The to_date converts the string to a date, the to_char converts back to a character string. The alternative would be to use substring and concatenation to do the same thing, but this is much easier.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

system · March 26, 2001, 3:20pm

Hello,

Thanks in advance for your reply…

We are using BO4.1.5 with DAS
I’m running a query against repository tables to get some dates from DAS i’m trying to retrieve some dates like submission dates and etc from DS_PENDING_JOB Table.
The date are storied in number(10) for all dates/time in this table do you any idea the format how they are storing?

Please let me know if you have any body idea on this one…

thanks
Srini

_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com

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

system · March 26, 2001, 3:24pm

Hello Srini,

The format is number of seconds after December 15th, 1970. You can get the normal date by using a query like:

to_date(‘15-DEC-1970’, ‘DD-MON-YYYY’) + (<table.date column> / 86400)

With regards,

Herjan Hakkers
Compex Netherlands

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

system · March 26, 2001, 3:27pm

I haven’t looked at this but is it just me or does this seem strange. Most normal systems use 1-JAN-1970 as the base for timestamps. Why would anybody do it with an apparently arbitrary date like this?

Ken.

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

system · March 26, 2001, 3:36pm

I have no idea, but this is a copy from the repository-documentation of BusinessObjects:

“The unit is elapsed seconds since the 15th of December 1970 at midnight.”

Maybe it’s the birthday of Bernard Liautaud?

Herjan

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

system · April 20, 2001, 1:29pm

Hi,

We are trying to get rid of the time stamps in the date field (especially in List of values).

I tried to_char(date) in LOV, but it is still returning time stamp along with date as mm/dd/yy 12:00:00, but the same function works fine at SQL prompt.

I tried the following to hide time stamp, but it is giving an #ERROR

="Report Period from “&FormatDate(ToDate(UserResponse (“Query 1 with Portfoli” , “2. Enter Start Date”) ,“mm/dd/yy”) ,“mm/dd/yy”)&” and "&FormatDate(ToDate(UserResponse (“Query 1 with Portfoli” , “3. Enter End Date”) ,“mm/dd/yy”) ,“mm/dd/yy”)

Is there something related to .SBO file etc…???

Can some one get back to me on this.

Pari

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

system · April 20, 2001, 1:49pm

The date format in LOV’s is controlled by the Regional Setting in the client. The short date format is used. Be careful if you change this as it will affect all other programs Excel etc…

Ken.

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

system · April 20, 2001, 2:08pm

Use trunc function for elinimating the time stamps. Good luck.

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

system · October 3, 2001, 9:45am

Hi All,

I’m trying to get the “Last action Date” from OBJ_M_ACTOR.M_ACTOR_N_LAD. This field is a numeric field when I put TO_DATE around it, it does not accept it.
My Oracle.Sbo file have the following
InputDateFormat=‘DD-MM-YYYY HH:MM:SS’
InitDateFormat=‘DD-MM-YYYY HH24:MI:SS’
AuditTimeStampFormat=‘DD-MM-YYYY HH:MM:SS’ and my ora7EN.prm have the following:
[DATE]
(SQL)
YEAR=TO_NUMBER(TO_CHAR($D,‘YYYY’))
QUARTER=TO_NUMBER(TO_CHAR($D,‘Q’))
MONTH=TO_NUMBER(TO_CHAR($D,‘MM’))

Do I nee to add something else to it?

Thanks,

Elie

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