date conversion from odd text value

[turbojo1]

I have a date value in text format that is 5 character and want to convert it to a proper date format. Here is a sample of the data:

07301 actually represents 7/30/2011. can anyone help me actually convert that value to the date format of mm/dd/yyyy?

thanks!

 

[Brianwarnock]

How did year 1 come to represent 2011, what would 2001, 2009 be? how abot 01/02/2013

Brian

 

[turbojo1]

a value such as 2009 would be 07309. doesn't make sense to me either but thats what I am stuck with. in the past the text value would just be separated into 3 different fields in a table, the year would then be corrected then all 3 concatenated into a date format. I was hoping for something quicker. my thought was to use perhaps the CDate and Format functions but I am stumped. Thanks.

 

[Brianwarnock]

You did not answer 2001 it cant be 07301 thats 2011, I was also trying to check that months and days always have 2 characters.Is the format you require your local format, if so I would use DateSerial, for the example it would be

Dateserial(Year(right(yourstring,1)+2010,month(left(yourstring,2,days(mid(yourstring,3,2))

But this fails on anything other than 2010? to 2019

Brian

 

[Pat Hartman]

There might have been some justification for this type of encoding back in the 60's (which ultimately led by the way to the Y2K crisis) when storage was enormously expensive and every single bit counted but today, I hate to equivocate, this is an idiotic solution. I hope you are changing this format permanently rather than trying to live with it. When working with dates, it is important to have data actually defined as a date/time data type. If you have dates defined as strings (of any format), you loose all the built-in date processing functionality and have to roll your own functions.

 

[Brianwarnock]

Yes Pat , but we never went as far as single digit years!!

Brian

 

[jdraw]

Have you checked with the powers to be what this represents and what they expect? I don't think there is an algorithm of any sort to convert a 1 digit year to 2 digits.

Failing some "magic response" from the requestors of this, what steps do you envisage to resolve 1, 11,12, 13.......?

 

[Pat Hartman]

Yes Pat , but we never went as far as single digit years!!

Readers' Digest went even further and essentially caused themselves a Y2K problem every 18 months! They of course had huge master files and even larger transaction files so when they created this method in the 60's every bit counted. They stored dates as 1 byte. Don't forget each byte can store 255 values. They mushed older dates so 1 = 1950-1960, 2 = 1961-1965, 3 = 1966 - 1970 .... and later values represented single years. I don't remember the exact scheme they used but it essentially required "sliding left" every 18 months . The second part of the date was "run" number. They did 2 "runs" per week and there were 52 weeks in a year so they only needed 104 values to represent this for each year. So 208 values would represent the past two years and other values were higher level groupings. So the "date" could represent a specific run if it happened in the most recent two years. Otherwise, all you could get out of the field was a year. So values 0-42 represented years and values 43-255 represented "runs" during the past two years. So they would actually update the date of every record every two years and age out the value. As time moved on, 1 would be assumed to mean 1950-1970 and 2 would be 1971-1980, etc. so they would have 5 extra years of single digits available for newer stuff.