Hi Asmath, If I understand your flow correctly, you have ExecuteSQLRecord (or QueryDatabaseTableRecord) with CSVRecordSetWriter and the expected output format for Oracle DATE type would be DD-MM-YYYY in the CSV.
To achieve this, you'll need to configure CSVRecordSetWriter's "Timestamp Format" property as DD-MM-YYYY and also set the processor's "Use Avro Logical Types" property to true. The output format of your timestamp columns will not be affected by this. Explanation / technical background info on date/timestamp handling in NiFi in case of Oracle DB: For DATE data type, the Oracle driver returns a java.sql.Timestamp object by default. That's why the user needs to configure "Timestamp Format" instead of "Date Format". For TIMESTAMP data type, the Oracle driver returns an oracle.sql.TIMESTAMP object by default. It is not a regular java timestamp and no formatting will be applied to it. It will be added in its default format in the CSV (eg.: 2021-03-02 16:30:41.0). Other RDBMSs (typically) return DATE/TIMESTAMP values as java.sql.Date and java.sql.Timestamp respectively and the proper format (from "Date Format" vs "Timestamp Format" properties) can be applied to them in the NiFi flow. Hope it helps. Let us know if you are able to configure your flow to produce the date format you want. Best, Peter On Mon, Mar 1, 2021 at 7:35 PM KhajaAsmath Mohammed <[email protected]> wrote: > Hi Peter, > > This issue is occuring for the DATE field on oracle. There are other > fields which are timestampe in oracle for the same table. > > Sample Data in Oracle: Date Field type --> 01-NOV-20 > > May I know the field name and format that I need to use in csvrecordwriter > now? > > > Thanks, > Asmath > > On Mon, Mar 1, 2021 at 12:20 PM Peter Turcsanyi <[email protected]> > wrote: > >> Hi Asmath, >> >> I would try to specify the format in the Timestamp Format property. >> Oracle's DATE type can contain HH:mm:ss part and I believe it is >> converted to a Timestamp and the Timestamp Format is applied. >> >> Regards, >> Peter >> >> On Mon, Mar 1, 2021 at 6:42 PM KhajaAsmath Mohammed < >> [email protected]> wrote: >> >>> Hi, >>> >>> I have an issue where the csvrecordwriter is automatically converting >>> data from date to number. how to resolve this? >>> >>> any suggestions to change this? >>> >>> [image: image.png] >>> >>> Source : Oracle with Date format >>> >>> [image: image.png] >>> >>> Target: Sql-server into Date format >>> >>> >>> Thanks, >>> Asmath >>> >>
