Re: CSVRecordSetWriter - Date format
Hi Peter, It resolved the timestamp issue but I am still getting more characters in timestamp. I only need to get maximum of 23 characters. any suggetsions on how to trim this data from 2021-02-02 09:01:24.847756 to 2021-02-02 09:01:24.8 ? I have yyy-MM-dd hh:mm:ss.S in timestamp field for CSVwriter. EMPLID|EMPL_RCD|EFFDT|EFFSEQ|PER_ORG|DEPTID|JOBCODE|POSITION_NBR|SUPERVISOR_ID|HR_STATUS|POSITION_OVERRIDE|POSN_CHANGE_RECORD|EMPL_STATUS|ACTION|ACTION_DT|ACTION_REASON|LOCATION|TAX_LOCATION_CD|JOB_ENTRY_DT|DEPT_ENTRY_DT|POSITION_ENTRY_DT|SHIFT|REG_TEMP|FULL_PART_TIME|COMPANY|PAYGROUP|BAS_GROUP_ID|ELIG_CONFIG1|ELIG_CONFIG2|ELIG_CONFIG3|ELIG_CONFIG4|ELIG_CONFIG5|ELIG_CONFIG6|ELIG_CONFIG7|ELIG_CONFIG8|ELIG_CONFIG9|BEN_STATUS|BAS_ACTION|COBRA_ACTION|EMPL_TYPE|HOLIDAY_SCHEDULE|STD_HOURS|STD_HRS_FREQUENCY|OFFICER_CD|EMPL_CLASS|SAL_ADMIN_PLAN|GRADE|GRADE_ENTRY_DT|STEP|STEP_ENTRY_DT|EARNS_DIST_TYPE|COMP_FREQUENCY|COMPRATE|CHANGE_AMT|CHANGE_PCT|ANNUAL_RT|MONTHLY_RT|DAILY_RT|HOURLY_RT|ANNL_BENEF_BASE_RT|SHIFT_RT|SHIFT_FACTOR|CURRENCY_CD|BUSINESS_UNIT|SETID_DEPT|SETID_JOBCODE|SETID_LOCATION|SETID_SALARY|SETID_EMPL_CLASS|REG_REGION|DIRECTLY_TIPPED|FLSA_STATUS|EEO_CLASS|UNION_CD|BARG_UNIT|UNION_SENIORITY_DT|GP_PAYGROUP|GP_DFLT_ELIG_GRP|GP_ELIG_GRP|GP_DFLT_CURRTTYP|CUR_RT_TYPE|GP_DFLT_EXRTDT|GP_ASOF_DT_EXG_RT|CLASS_INDC|ENCUMB_OVERRIDE|FICA_STATUS_EE|FTE|PRORATE_CNT_AMT|PAY_SYSTEM_FLG|LUMP_SUM_PAY|CONTRACT_NUM|JOB_INDICATOR|BENEFIT_SYSTEM|WORK_DAY_HOURS|REPORTS_TO|JOB_DATA_SRC_CD|ESTABID|SUPV_LVL_ID|SETID_SUPV_LVL|ABSENCE_SYSTEM_CD|POI_TYPE|HIRE_DT|LAST_HIRE_DT|TERMINATION_DT|ASGN_START_DT|LST_ASGN_START_DT|ASGN_END_DT|LDW_OVR|LAST_DATE_WORKED|EXPECTED_RETURN_DT|EXPECTED_END_DATE|AUTO_END_FLG|LASTUPDDTTM|LASTUPDOPRID|CR_BT_DTM|CR_BT_NBR|UPD_BT_DTM|UPD_BT_NBR|ODS_VRSN_NBR|DML_IND 10474406|0|2020-11-01 00:00:00.0|0|CWR|000286|CWR015|" "|" "|A|N|N|A|ADD|2021-02-02 00:00:00.0|ADD|VCAA|CA-DEFAULT|2020-11-01 00:00:00.0|2020-11-01 00:00:00.0||N|R|F|UCS|CWR|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|A|" "|" "|S|" "|40|W|N|13|" "|" "||0||N|M|0|0|0|0|0|0|0|0|0|0|USD|SDCMP|SDCMP|UCSHR|SDCMP|" "|STD|USA|N|X|N|" "|" "||" "|Y|" "|Y|" "|Y|" "|C|N|N|0|N|OT|N|" "|P|OT|0|" "|" "|UCSD|" "|" "|OT|" "|2020-11-01 00:00:00.0|2020-11-01 00:00:00.0||2020-11-01 00:00:00.0|2020-11-01 00:00:00.0||N|||2021-10-31 00:00:00.0|N|*2021-02-02 09:01:24.847756*|UC_BATCH|2021-02-02 09:01:25.0|2625|2021-02-02 09:01:25.0|2625|211|I Thanks, Asmath On Tue, Mar 2, 2021 at 10:52 AM KhajaAsmath Mohammed < mdkhajaasm...@gmail.com> wrote: > 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 > 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- in the CSV. >> >> To achieve this, you'll need to configure CSVRecordSetWriter's "Timestamp >> Format" property as DD-MM- 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 < >> mdkhajaasm...@gmail.com> 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 >>> 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 < mdkhajaasm...@gmail.com>
Re: CSVRecordSetWriter - Date format
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 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- in the CSV. > > To achieve this, you'll need to configure CSVRecordSetWriter's "Timestamp > Format" property as DD-MM- 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 < > mdkhajaasm...@gmail.com> 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 >> 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 < >>> mdkhajaasm...@gmail.com> 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 >>>
Re: CSVRecordSetWriter - Date format
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- in the CSV. To achieve this, you'll need to configure CSVRecordSetWriter's "Timestamp Format" property as DD-MM- 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 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 > 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 < >> mdkhajaasm...@gmail.com> 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 >>> >>
Re: CSVRecordSetWriter - Date format
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 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 < > mdkhajaasm...@gmail.com> 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 >> >
Re: CSVRecordSetWriter - Date format
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 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 >
CSVRecordSetWriter - Date format
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
