Thanks Peter, this worked. I have now issue with csv reader and loading that into SQL server. Will send out seperate email for that.
On Tue, Mar 2, 2021 at 9:37 AM Peter Turcsanyi <[email protected]> wrote: > 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 >>>> >>>
