August 19, 2011 at 2:21 pm
Just a random question that I had while doing some SSIS development.
I'm dealing with some flat files where the date is sometimes presented in an MM/DD/YY format. Outputting those into SQL Server requires me to do a bit of pre-processing to set the dates up in a way that is compatible with the SMALLDATETIME or DATETIME data types.
The way that I have been doing that, since it's the fastest and simplest way, is just to append "20" to the year part, to convert it into a YYYY-MM-DD format.
The thought just occurred to me that this is probably what all the engineers in the 70's and onwards had been doing, and which resulted in the Y2K "crisis". Of course, for that one it was amplified even more because of data types which simply didn't have the capability of handling dates greater than 1999, but in my case the problem still exists - when the years roll by and 2100 hits, the dates will no longer work!
This is of course a very extreme scenario - I'm pretty sure this SSIS package is not going to be run for another 90 years in the way it is written. But, of course, I'm sure those engineers in the 70's thought the same thing about their code.
So I'm just curious - how far do you go to ensure that your work will continue to function over the years? Where is the line that you draw before you conclude that there's no reason to spend the extra time and effort to craft your work in a way that is completely future-compatible?
Personally, I strive to make my work as adaptable as possible, for instances which I think could conceivably occur while I'm working with the same project. Planning my work to be adaptable for future workers is sometimes part of my scope, but often there simply isn't enough time to really design the work to be that adaptable. It's the kind of thing that would probably get me failing grades in my Computer Engineering degree, but it's also the kind of thing that occurs all too often in the workplace.
September 4, 2011 at 6:15 pm
As a general rule, I will go a long way to ensure my code remains valid with changing dates (in the 1970s I was working in an outfit that used a 64 bit signed count of microseconds since 1901-01-01T00:00:00.000000 for times, so none of our code would ever have a year 2000 problem or even a year 200000 problem). But when you are coverting something that uses mm/dd/yy format you are stuck with the limitations of mm/dd/yy format. There isn't any algorithm that will allow you to pick the correct 4 digit year from the supplied 2 digit year unless there are clear business rules that allow you to dtermine the required result. If there are such business rules, you probably ought to implement them, if there are no such rules then you can't usefully do anything except document the problem and make sure the documentation is pervasively distributed.
The business rules you should be looking for are things something like "No date I have to translate is ever more than 10 years in the past" and "No date I have to translate is ever more than 2 years in the future". I think you must have such rules, since the method you are already using assumes that no date to be converted is more than 12 years in the past. Once you have identified some rules that will continue to apply as time goes forward, you have at any time a period within which a 2 digit year must lie, and if that period is shorter than a century (as it almost certainly will be for most businesses) you will know what the 2 digits you need to prefix are (the period may cross a century boundary, but you know which side of the boundary the date lies because the 2 digit year would put it out of range on one side). You may also be able to catch some errors (date not within the allowed range - probably a data entry error) which may be more important and useful than having code that remains valid for centuries, but uses exactly the same mechanism that keeps the code valid long-term.
Tom
September 5, 2011 at 11:18 am
I was ready for Y2K in 1980 and I'm ready for 2079-06-06 right now. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2011 at 11:56 am
I seriously have to work with a vendor database that has a Y10 issue. Yes, a Y10 issue. They use counter fields to "increment" when the decade changes. It's that kind of thinking that makes me want to go into the parking lot and light myself on fire. I always assume that whatever I'm doing will always be used. How many globs of data have we upgraded from one form or another to assure continued data in a different platform? 60-70's MF to 80's MF to 90's RDMS's to now, "NoSQL" systems (don't get me started on that). If in the 60's assumptions weren't made about scalability, the whole process becomes a lot simpler....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply