OraFAQ Forum: Reports & Discoverer » report errors need help on query multiple date format in table

Home » Developer & Programmer » Reports & Discoverer » report errors need help on query multiple date format in table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

report errors need help on query multiple date format in table [message #613713]

Fri, 09 May 2014 09:51

tomrubble
Messages: 3
Registered: May 2014

Junior Member

The rdf has existed since 2007 working and had no problem . Someone changed the value set with a new one which caused a problem. When they tried to change it back to the origninal value set they couldnt because it is now obsolete. So the new value set stores the attribute as YYYY/MM/DD 00:00:00 the obsolete v.s stores the data as DD-MON-YYYY. I was told that the old vendors attribute14 must stay at DD-MON-YYYY and the new vs will now be in YYYY/MM/DD 00:00:00 . So the table column will now hold a mixed date format.

The query in the rdf is

SELECT pov.vendor_name , pov.attribute14
FROM po_vendor_sites pvs
, po_vendors pov
WHERE pov.vendor_id = pvs.vendor_id
AND pov.attribute15 = 'Y'
AND pov.attribute14 = to_char(to_date(SUBSTR( _date_request ,1,10),'YYYY/MM/DD HH24:MI:SS') , 'DD-MON-YYYY')

So since there is two types of formats now the query errors out

So i need to correct the query to look at both date formats. I did this ..............

i created a test table with attribute14 I have placed this data in it

-------------------------------------------------------------------------------------------------------------
If the 4 records are in the DD-MON-YYYY as 30-MAY-2014 it works when all this way DD-MON-YYYY
29-MAY-2014
30-MAY-2014
28-MAY-2014
30-MAY-2014

SELECT * FROM xx pov
WHERE TO_DATE (pov.attribute14, 'DD-MON-YYYY')
BETWEEN TO_DATE ( _date_request_from, 'YYYY/MM/DD HH24:MI:SS')
AND TO_DATE ( _date_to,'YYYY/MM/DD HH24:MI:SS')
-------------------------------------------------------------------------------------------------------------
If the 4 records are in 2014/05/29 00:00:00 it works

2014/05/30 00:00:00
2014/05/31 00:00:00
2014/05/29 00:00:00
2014/05/31 00:00:00

SELECT * FROM xx pov
WHERE pov.attribute14 BETWEEN _date_request_from AND _date_to
-------------------------------------------------------------------------------------------------------------

....... BUT ......................
If i mix them
29-MAY-2014
30-MAY-2014
2014/05/29 00:00:00
2014/05/31 00:00:00

I tried multiple ways w/ decodes truncs substr etc. and i get errors like this
--ORA-01858: a non-numeric character was found where a numeric was expected
-- ORA-01861: literal does not match format string

So i need help w/ the query to work to look at both types of date formats without error-ing out

thanks.

Report message to a moderator

Re: report errors need help on query multiple date format in table [message #613741 is a reply to message #613713]

Fri, 09 May 2014 15:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If I understood correctly what you are saying: if you can store both (or even more) formats in a table, it means that column data type is VARCHAR2. Yet another example of wrong data model. Dates should *always* be stored into a DATE data type column. If I'm wrong, I'm still right (always store dates into DATE data type columns).

Here's what you could do: create a function which will accept - as a parameter - value from the table (that would be either '29-MAY-2014' or '2014/05/29 00:00:00'). The function will convert input into output which has valid format ('DD-MON-YYYY') and return result which is suitable for your report. You'll have to handle any exception you might meet (such as the two you already mentioned, ORA-01858 and ORA-01861). It means that code might have (embedded?) several BEGIN-EXCEPTION-END blocks until you succeed to compose the correct result.

Report's query would then use a function (instead of the value itself), such as

SELECT pov.vendor_name, 
       pov.attribute14
FROM po_vendor_sites pvs,
     po_vendors pov
WHERE pov.vendor_id = pvs.vendor_id
  AND pov.attribute15 = 'Y'
  AND pov.attribute14 = your_function(_date_request)  --> this!

Report message to a moderator

Re: report errors need help on query multiple date format in table [message #613799 is a reply to message #613741]

Mon, 12 May 2014 07:05

tomrubble
Messages: 3
Registered: May 2014

Junior Member

I got this issue figured out
and DECODE (LENGTH (NVL (pov.attribute14, '01-JAN-1900')), 11, TO_DATE (pov.attribute14, 'DD-MON-YYYY'),19, TO_DATE (SUBSTR (pov.attribute14, 1, 10), 'YYYY/MM/DD')) BETWEEN to_date( :p_date_request_from, 'YYYY/MM/DD HH24:MI:SS')
AND to_date ( :p_date_to, 'YYYY/MM/DD HH24:MI:SS')

Thanks...

Report message to a moderator

Previous Topic:	excel output in 9ias
Next Topic:	How to add Error Msg when format is XML Oracle Repor Builder 6i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 07:01:37 CDT 2025