Date Format Issue

[stuartclements]

Hi,

We have built an oracle function used as an 'Age' object in a BO universe. The function compares two dates and returns the difference in years.

Age Object Definition:
f_bo_empl_age(PS_PERSON.birthdate,@Prompt('for which date','D',,MONO,FREE) )

A problem is occuring when a query is run on Webi using the object. Assume a persons birthdate is 20/10/71 (UK Format)

When the query is run with the date: 17/11/03 the age is returned as 46 (This is correct if the date is taken as year 2017)

When run with a date: 11/17/03 (US Format) the query correctly returns the age of the person as :32

This issue seems to be related to the @Prompt function and how BO interprates the date entered by the user.
Has anyone seen this problem before?

We are using BO5.1.7 running against an Oracle 9i DB.

Thanks
Stuart

 

[blom0344]

I think ORACLE uses a default date format. If you do not modify it, it is:

'dd-mon-yy'

If you use another format you will have to use a formatting option to tell ORACLE.

Possible your format is set to: mm/dd/yy.

Since 17 is not a valid month, it may interpret the date as
yy/mm/dd

Your ORACLE DBA will know all about this I guess....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[nanmnzr]

We are facing the same issue in the date format.
in order to fix it because all the sales figures were wrong due to this error, we decided to use the date with "dd/month/yyyy" format,but still we have 2 problems:
1)it's very annoying to write the whole month name
2)on some computers, this format doesn't work , so they still have to write the format as dd/mm/yyyy and they don't know if the system consider it as dd/mm/yyyy or mm/dd/yyyy.
Thank you for telling me what to do, or at least tell me what should i tell our DBA to do.

Thanks a lot.

 

[blom0344]

You do not have to use the full name I think. 'MON' should work with the 3 character code for months (like 'AUG').
If you have some PC's that not function properly this way, did you check if this is due to other regional settings with respect to the date formatting in Windows?

T. Blom
Information analyst
tbl@shimano-eu.com