Oracle Date as Datetime

[Former Member]

Hello,

I am trying to work with data I am pulling form a table in an Oracle 11gR2 Database.

I am connected with the Oracle Native client and am using Crystal Reports 2013 14.1.4.1327

I have a field called MONTH that I am selecting into a report.

I have tried setting this field as DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIME ZONE .

It doesn't matter how I set the Datatype on the Oracle server I am always ending up with

1) a DateTime datatype in Crystal Report

2) the datetime is offset by a number of hours which leasves me with the wrong month Example: '01-AUG-2015' becomes '26-JUL-2015 3AM' in Crystal

Why doesn't crystal pick up that I am sending a Date? Why does it change the time on me? Is there something I can do to fix this that will work as well in PEI as it will in Houston?

[DellSC · Accepted solution]

Oracle stores dates as datetime, which is what Crystal will see.  If you're just connecting tables together for your report, try creating a SQL Expression that will truncate the date:

Trunc("MyTable"."Month")

If you're using a command or a stored proc, then you'll do this to the field in the command or SP instead of in a SQL Expression in the report.

-Dell

[Former Member · Accepted solution]

Turns out I don't need to go that far Dell.

While Oracle is providing the date properly the Crystal Report software default for

'Group Option'

Setting 'This Section will be printed' to the value 'For Each Week'

Changing this to 'For each month' fixed the formatting.

All fixed, Whew!