Oracle Queries - Time not correct

[Former Member]

When we execute an Oracle query from MII all fields with an Oracle datatype of DATE are not displaying correctly. The date (MM/DD/YYYY) is fine, but the time portion is displaying as all 0's (00:00:00). Does anyone have any ideas of how to correct this? I have verified the data in the source Oracle database and the data does include times. Thanks.

[jamie_cawley]

Check out note 1124769.

Jamie

[Former Member]

Thanks - not what I want to see, but at least I have an answer. In my search I did see another note that mentioned it might be an issue with the version of jdbc drivers - although the note you mentioned clearly states it is an issue with Oracle's internal handling of Dates. I think I will pursue an attempt at updating the jdbc drivers before taking the drastic step of updating all the date fields to timestamp. Thanks again.

[Former Member]

SELECT SYSDATE FROM DUAL gives me 2011-01-17T00:00:00

Then,

select * from

(SELECT * FROM EVENT_LOG ORDER BY EVENTTIMESTAMP DESC)

where ROWNUM <= 1

returns

EVENTID EVENTTIMESTAMP EVENTDESCRIPTION EVENTSEVERITY

881458.00 01/17/2011 16:04:45 PMU OK ROUTINE

The ojdbc14.jar driver works ok with oracle timestamp fields. Usually I convert all dates to timestamp (as below) and the jdbc driver and/or oracle client on the MII box adapts even to different timezones (i.e. it knows that the client is -3h if the database in EST timezone and the MII box/client is in PST timezone).

The following deletes everything from the logs older than 3 months (as related to the date.time.milis when I run it).

DELETE FROM EVENT_LOG WHERE EVENT_LOG.EVENTTIMESTAMP <= to_timestamp(ADD_MONTHS(SYSDATE,-3))

-

---

short answer .. try to_timestamp() applied on your date fields on the Oracle side, why is this considered drastic?

[agentry_src]

It is and oldie, but a goodie:

[TO_CHAR|]

Regards,

Mike