Date Set Not allowing Date property as mm/dd/yyyy
We are looking to set a Date property as mm/dd/yyyywhile mapping from a file data set.
Data Flow Source is File Data Set:
CSV File has a column with "mm/dd/yyyy" format value ex: 5/14/2024.
In the Mapping Tab, a Date Property (Type: date) mapped to the excel column.
Getting Error while running the data flow. Bad Mapping Error.
Requirement is to store column value in this date property and save back to the DB table in a date type DB column.
Any suggestions would be helpful!
Accepted Solution
Updated: 3 Jul 2024 3:58 EDT
Evonsys
IN
In Dataset rule, below you will find Date time Format and Date format and Time Of Day format fields.Check whether the format in Date format field same as CSV file date column format.
For CSV and JSON files, update date and time settings:
- In the Date Time format field, enter the pattern representing date and time stamps in the selected file.
The default pattern is: yyyy-MM-ddHH:mm:ss
- In the Date format field, enter the pattern representing date stamps in the selected file.
The default pattern is: yyyy-MM-dd
- In the Time Of Day format field, enter the pattern representing time stamps in the selected file.
The default pattern is: HH:mm:ss
Pegasystems Inc.
CA
Please use
@DateTime.FormatDateTime(@DateTime.CurrentDateTime(),"MM/dd/YYYY'T'HH:mm:ss", pxRequestor.pyDefaultTimeZone, "")
Ernst & Young LLP
IN
@RameshSangiliThanks for the response.
Where can we apply this function?
In the file dataset mapping tab, we are referring a Date type property and the excel file has value as 'mm/dd/yyyy'.
We are trying to store in this property and save back to the DB table in a date type column.
Thanks! Priyesh A
Ernst & Young LLP
IN
@RameshSangiliThis worked but was looking for a OOTB Solution, without any mapping.
Accepted Solution
Updated: 3 Jul 2024 3:58 EDT
Evonsys
IN
In Dataset rule, below you will find Date time Format and Date format and Time Of Day format fields.Check whether the format in Date format field same as CSV file date column format.
For CSV and JSON files, update date and time settings:
- In the Date Time format field, enter the pattern representing date and time stamps in the selected file.
The default pattern is: yyyy-MM-ddHH:mm:ss
- In the Date format field, enter the pattern representing date stamps in the selected file.
The default pattern is: yyyy-MM-dd
- In the Time Of Day format field, enter the pattern representing time stamps in the selected file.
The default pattern is: HH:mm:ss
Ernst & Young LLP
IN
@KishoreKumarMadduri This seems to be working.
The value (06/24/2024) got saved to a date property as '20240624' but not getting saved to DB Column of date type.
getting below error:
com.pega.pegarules.pub.database.BadTableMappingException:
table abc has an unknown type: JDBC Type: Date
any idea on this? Thanks!
Eclatprime Digital Private Limited
IN
Hi @Priyesh
In your Data Flow, ensure that the Date Format specified for the CSV file matches the actual format of the date column. You can find this setting in the Data Set configuration. If your CSV file has dates in “mm/dd/yyyy” format, set the Date Format accordingly.
Create a data transform to convert the CSV date value to the desired format for your Date property. Use the @DateTime.FormatDateTime function to transform the date value.
@DateTime.FormatDateTime(@DateTime.CurrentDateTime(), "MM/dd/yyyy", pxRequestor.pyDefaultTimeZone) This function converts the current date to the “mm/dd/yyyy” format.
In the Mapping Tab, ensure that the Date Property is correctly mapped to the transformed date value.
Ernst & Young LLP
IN
Thanks!
Post selecting the format in Data set setting, date property is holding value (in yyyymmdd), but not getting saved to the db column.
DB column is of date type.
Error:
com.pega.pegarules.pub.database.BadTableMappingException:
table abc has an unknown type: JDBC Type: Date
Thanks! Priyesh A
Evonsys
IN
@Priyesh Can you check the db column data type properly.
please refer this, would be helpful: https://support.pega.com/question/having-badtablemappingexception-after-updating-722
Ernst & Young LLP
IN
The Column was created as 'Date(13)' not sure if that's correct or not, but that's what it was created by DBA at backend.
The article mentioned above seems to be for MessageTimeStamp, where we have date as column type.
Thanks!
Priyesh A
Eclatprime Digital Private Limited
IN
Hi @Priyesh
Verify that the database columns have the correct data types. Pega may not support certain column types.
If you encounter issues with date-related columns, consider altering the column type to TIMESTAMP.
For dates, Pega typically uses data types like DateTime, Date, or Time. Ensure that the Pega property corresponds correctly to the database column type.
Sometimes, JDBC drivers might not fully support certain data types or their mappings. Ensure that you are using a JDBC driver version that is compatible with the data types in your database. Updating or changing the JDBC driver version might resolve compatibility issues.
Ernst & Young LLP
IN
As per the below doc,
For PostgresSQL, date property in pega can only support DB column as varchar(8)
and even if we want to use Date column as 'Date', we need to pass as yyyy-MM-dd, where as Date property in pega supports or holds yyyyMMdd.
I tried saving a text property value as yyyy-MM-dd value to a 'Date' property but it is not changing (keeping as yyyymmdd only). Also tried datetimeformat function but no help.
Thanks!
Priyesh A