ToDate function is not working for bulk data

[SaravanakumarB1]

Hi ,

Am trying to convert the Last Pay date string value("20310229") to Date function in Enrichment activity. But it is not working for larger data almost 11lakhs above data.  I had random sampled with 550 that time it is working and getting result properly. But when i try to pass the bulk one after enrichment we get 0 value and below mentioned ERROR appears:

My Query: ToDate(ToDateTime("Field vlue"))

01/03/2026 2:27:50 PM enrich PGS-220000 PostgreSQL error: ERROR: date/time field value out of range: "20310229" .

Thanks,

Saravanakumar

[ParthaSarathy · Accepted solution]

Hi @SaravanakumarB1 ,

The expression you have mentioned is correct. You're getting this error not because of bulk data, but because of invalid data. 

20310229 (YYYYMMDD) as 2031st doesn't have 29th February, you're getting the error PostgreSQL error: ERROR: date/time field value out of range: "20310229".

[AishwaryaReghu]

Hi @SaravanakumarB1 ,

The Root Cause is the date entry that you provided, February 29, 2031!!

The error date/time field value out of range: "20310229" is occurring because February 29, 2031, does not exist.

2031 is not a leap year.

Before performing the ToDate conversion, you should identify and fix the source data. You can add a Filtering Dimenson or a Filter activity before your Enrichment to find these records:

Filter criteria:

Last Pay Date LIKE '%0229' AND Last Pay Date NOT LIKE '2028%'

(and other valid leap years).

Alternatively, use a regex to identify invalid February dates.

If you want the workflow to finish regardless of bad data, you can use a Case statement in your Enrichment to "nullify" or default the invalid dates so the function doesn't crash.

SQl:
Case When $([YourField]) = '20310229' Then NULL Else ToDate(ToDateTime($([YourField]))) End

Regards,

Aishwarya.

www.linkedin.com/in/aishwarya-r-370808158