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 < [email protected]> 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 <[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 >>>>> >>>>
