When Searching on a Date/Time Field, the SQL Generated for the Search is adding 8 Hours (Doc ID 740459.1)

Last updated on JULY 09, 2014

Applies to:

Oracle Transportation Management - Version: 5.5.05 to 5.5.05.01
This problem can occur on any platform.

Symptoms

-- Problem Statement:

After installing OTM Version 5.5 CU5, when searching by Date fields on the Finder Screens, the date fields are inserting a time shift of 08:00. For example, when a Shipment Search is done by entering the Start Time of 2008-10-01, the following SQL statement can be seen:

select sh.shipment_gid, sh.shipment_gid from SHIPMENT sh, SHIPMENT_TYPE stp where (sh.start_time between trunc(TO_DATE(?,?), ?) and trunc(TO_DATE(?,?), ?)+0.99999) and (sh.perspective='B') and (sh.shipment_type_gid=stp.shipment_type_gid) and (stp.shipment_type_xid!=?); [2008-10-01 08:00:00, YYYY-MM-DD HH24:MI:SS, MI, 2008-10-01 08:00:00, YYYY-MM-DD HH24:MI:SS, MI, NON FREIGHT RELATED CHARGE]

Notice the time shift of 08:00

-- Steps To Reproduce:
Run a buy shipment search by entereing a Start Time date

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms