Help w/ "where TRUNC(date_field)"
830722Apr 4 2011 — edited Apr 5 2011I'm a "newbie" to pl/sql - Oracle and I'm hoping someone can help me.
I need to extract records based on a given date range and I've put together the following query:
SELECT *
FROM abc.table
WHERE TRUNC(ld_dt) = TO_DATE('11-SEP-10','DD-MON-YY')
When I run this thru SQLplus or ODI any date fields output as 04/11/2010 12:00:00 (or 00:00:00) - I'm loosing the time portion of the field... I've narrowed it down to the WHERE TRUNC(). This query runs correctly in SQLplus & ODI if I remove the WHERE clause and format any date columns as TO_CHAR(ld_dt,'MM-DD-RR HH:MI:SS')... Its been a bit mind-boggling because it works in TOAD but I need it to work with SQLplus or ODI.
NLS_DATE_FORMAT: DD-MON-RR
NLS_DATE_LANGUAGE: AMERICAN
We’re running ODI 10.1.3.6.2 – 10g R2 Database
Thanks !