You can create a simple function that will separate the parts and add them together.
This will separate out the month, then add the first day of the month, then add the year. The date around the function forces it to be recognized as a date.
date(right(datestring,1) + '/1/' + left(datestring,4)) = 9/1/2016
Or you could use the dateparse function dateparse('yyyyM',datestring) if your datasource will allow it.
Thanks Alex. Dateparse didn't work for me but date function certainly works.
Appreciate your help here.
I would be cautious though, depending on how your date field handles October, November, and December.
if for months 1-9 its 20169 and 10-12 its 201610, you may need to do
if len(datestring) = 5 then parse 1 number month
else parse 2 number month.
or use the mid function instead
Otherwise if its 201601 then you will be fine to parse the right 2 as the month.