Text To Date Calculating Incorrectly

Hi All

I am asking registrants to capture an ID number and a birthdate as part of the data in a database. Since the first 6 digits of the ID number correspond to an individual’s birth date in the format yymmdd, I am trying to validate the two captures by comparing the values to test that they match.

I sliced out the first 6 digits of the ID number captured, used a template column to “build” a date made from the sliced components, and then use the Text to Date column to create a date against which i can compare the captured birth date value.

The result I am getting is that the day element in the Text to Date date is a day earlier than I would expect given the “sliced” dd value?

Appreciate any suggestions!

I think it might have something to do with timezones here. I seemed to have faced this problem a few times already, so I don’t use date plugins for use cases like this anymore.

I think you can take an alternative approach of converting them to a numeric value.

  • ID number: Slice first 6 numbers. I’m not sure if this returns a text or a number. If it doesn’t work directly then maybe use a math column pointing to the slice result to convert it to a number.

  • DOB in date form: Convert to numeric value by using a math column.

MOD(YEAR(D),100)*10000 + MONTH(D)*100 + DAY(D)

with D being the DOB.

Then compare the two values together to see if they match.

1 Like


Try with another Date plugin and procedure, I have used this for something similar and works fine

Thank you @ThinhDinh - this worked - I did have to convert the “sliced” text via a math column to handle values from years 2000 to 2009 where the sliced text includes leading zero’s, not compatible with a math test to compare with the converted DOB.

Thank you!

@gvalero thanks for suggestion - just to note my input data is a text string not a date hence the format plugin not valid in this use case.


This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.