I have a field, PERIOD_ID, that is in a text format, I am trying to convert this into an actual date format:
an example of what the data looks like in this field is 0208 this correspondes to August 2002.
I have tried the format function by doing this:
format((mid([period_id],3,2) & 20 & left([period_id],2), "mm/yyyy"
I would expect this to return 08/2002, however the dates in the return are way off from the original data in the PERIOD_ID field.
For example, the 0208 returns 07/2124 as the date.
I have tried doing this in 2 separate steps:
format(mid([period_id],3,2),"mm"
format(20&left([period_id],2),"yy"
And get the same results - 07 for MM and 2124 for YYYY (for the 0208 period_id).
I have also tried to convert these into regular integers, then convert into dates, then concatinate them together.
Nothing is working so far, any help would be grateful!
an example of what the data looks like in this field is 0208 this correspondes to August 2002.
I have tried the format function by doing this:
format((mid([period_id],3,2) & 20 & left([period_id],2), "mm/yyyy"
I would expect this to return 08/2002, however the dates in the return are way off from the original data in the PERIOD_ID field.
For example, the 0208 returns 07/2124 as the date.
I have tried doing this in 2 separate steps:
format(mid([period_id],3,2),"mm"
format(20&left([period_id],2),"yy"
And get the same results - 07 for MM and 2124 for YYYY (for the 0208 period_id).
I have also tried to convert these into regular integers, then convert into dates, then concatinate them together.
Nothing is working so far, any help would be grateful!