Oracle Date Conversion Issue

building semantic layer
1

I need help on Date conversion; although this has been discussed many times in the past, but none of the solution working for me.
I’ve S_Date field in Oracle, which is in DATE type and stores in “DD-MON-YYYY” format.
When I mapped this S_Date in UDT, UDT shows it in “MM/DD/YYYY” format.
My Webi report has Date prompt which request date in “MM/DD/YYYY” (obviously). If I enter the date as “01/01/2001” in prompt then the report does not return any data.
If I enter prompt date as “01-JAN-2001” then I get Oracle and WIS-10901 BI error as an invalid format.
In Universe, I tried to convert the S_Date object to “TO_CHAR(S_Date, ‘DD-MON-YYYY’)”. I then used the new S_Date format in Report prompt and entered the date as “01-JAN-2001”.
Report ran just fine but with garbage data. It returned records that are not related to “01-JAN-2001”, returned records from 1970, 1980, 1990 and so on.
I copied the report SQL and ran it in SQL Developer, which returned the garbage data as I saw in Webi report.
I started playing with it and found that the issue is actually caused by the date conversion “TO_CHAR(S_Date, ‘DD-MON-YYYY’)”. When I changed the report SQL, remove
Date conversion, use S_Date as is then it returns correct results.
But then UDT changes the format to MM/DD/YYYY so How do I correct this issue?

Thanks for your help.

zulu1900 (BOB member since 2005-07-27)

2

I think my issue is resolved (99% I think). It’s pretty much software solution, not a coding solution. Here is what I did and working as expected:

  1. In SQL Developer, Tools–>Preferences–>Database–>NLS. Changed date format to DD-MON-YYYY to confirm that all dates are indeed returning in the format defined.
  2. In UDT Universe, Right click S_Date object -->Object Format–>Date/Time–>Properties (properties windows on bottom)–>Type dd-Mmm-yyyy–>Add
    Parsed the object and made sure it works.
    Saved the universe and exported to repository.
    Now, when I run the report the S_Date object still shows in “MM/DD/YYYY” format but this time I can enter any date in that format and report returns correct data.
    I’ve created SAP ticket as to why the Universe date format is not changing the date display. They are investigating it.
    Hope this helps others who are experiencing similar issue.

zulu1900 (BOB member since 2005-07-27)

3

Easy question first - are you certain there is data for January 1, 2001?

joepeters :us: (BOB member since 2002-08-29)

4

Yes there are 100s of records.

zulu1900 (BOB member since 2005-07-27)

5

Ok, good.

This part caught my attention:

By doing a to_char, you’re converting the data to a plain old string. There’s no good reason why filtering on a string would produce different results, so I’m interested to see what the SQL was that produced that “garbage” data. Can you share the SQL and a sample of the data it produced?

joepeters :us: (BOB member since 2002-08-29)

6

joepeters,
I completely forgot about this thread as my issue seems to have resolved. I’ve updated this thread with the solution that worked for me.
As far as your question on sample sql that produced garbage data, it was similar to any other sql but I think this is how it was:

SELECT column1, column2, column3, S_Date
FROM table1
WHERE TO_CHAR (S_Date,‘DD-MON-YYYY’) >= ‘01-JAN-2001’

I was getting results from 2000 and even from 90s. Probably my comparison was wrong, comparing string to date :slight_smile:

Thanks

zulu1900 (BOB member since 2005-07-27)

7

Yes, exactly. If you had formatted it as y-m-d, you would have gotten expected results. As it is, ‘01-jan-1970’ will come before ‘01-mar-2017’.

Joe

joepeters :us: (BOB member since 2002-08-29)

8

:lol: :lol: :lol:
when u transfor the date type to string, ‘01-JAN…’ is sure ‘01-JAN-XXXX’ is sure larger than ‘01-MAR-XXXX’ .
try this :
SELECT column1, column2, column3, S_Date
FROM table1
WHERE S_Date>= to_date(‘01-JAN-2001’,‘DD-MON-YYYY’)

Mophi :cn: (BOB member since 2009-11-10)