Date Set Not allowing Date property as mm/dd/yyyy

General
,
1

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!

2

@Priyesh

Please use

@DateTime.FormatDateTime(@DateTime.CurrentDateTime(),“MM/dd/YYYY’T’HH:mm:ss”, pxRequestor.pyDefaultTimeZone, “”)

3

@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

4

@Priyesh

I think you can add a data transform to massage the data based on your requirements.

5

@Priyesh:

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:

  1. In the Date Time format field, enter the pattern representing date and time stamps in the selected file.
    The default pattern is: yyyy-MM-dd**HH:mm:ss

  2. In the Date format field, enter the pattern representing date stamps in the selected file.
    The default pattern is: yyyy-MM-dd

  3. In the Time Of Day format field, enter the pattern representing time stamps in the selected file.
    The default pattern is: HH:mm:ss

6

@KishoreKumarM2533 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!

7

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.

8

@Priyesh

You’re using OOTB FormatDateTime function to covert the date to your required format.

9

@MahavishnuN04

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

10

@Priyesh Can you check the db column data type properly.

please refer this, would be helpful: Having BadTableMappingException after updating to 7.2.2 | Support Center

11

@KishoreKumarM2533

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

12

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.

13

@MahavishnuN04

As per the below doc,

Pegasystems Documentation.

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

14

@RameshSangiliThis worked but was looking for a OOTB Solution, without any mapping.