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?
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.
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.
