Y2K Strikes Again!
Introduction
For me at least. I'm sure the rest of you are ok, but I got bitten by a bug just this morning that I hadn't seen or expected since just after the Y2K fiasco. It's a software bug, one totally introduced by me, but one which I have run with for the over a year without noticing.
I have an application that records current notes for the day for a department. It's kind of like a status report that shows the current entries for today. The application allows people to enter notes, timestamps them along with the user name, and allows information to be shared amongst a decent sized group of people.
So I came in today and someone immediately pointed out that when they had logged in at 5:00am this morning (a god awful time, why anyone would be up then is beyond me), there were already notes listed from 2:00pm.
My first thought was that someone was playing a joke. Some other department had hacked in and put up something to see if we were paying attention. That type of thing happens fairly often around here, woe to the person who doesn't lock their workstation and walks away. So I started checking for entries in the database.
None to be found.
Hmmmm. Kind of annoying and interrupting my morning coffee. This application has been running since late 2000 and worked fairly flawlessly, requiring almost none of my time. So I started poking around in the code and came across this query that gets the current day's entries:
select entrydate , msg , title , catname from msg m inner join category c on m.catid = c.catid where datepart( m, m.entrydate) = datepart( m, getdate()) and datepart( d, m.entrydate) = datepart( d, getdate()) and datepart( y, entrydate) = datepart( y, getdate()) order by entrydate
I remembered writing this code and it's been working. However when I run it, I get a message from last year. If I check the table, I see
What's Wrong
Most programmers with a small amount of experience probably quickly caught the bug above. It was sloppy programming on my part, but something which worked in development and testing, so I didn't check too closely.
We need to timestamp the entries so we know when they were made. As such, the entrydate is stamped with GETDATE() when an entry is made. We only want the entries for today and we don't know how many there will be (ranges from 0 to a few dozen per day), so I joined using the datepart() function for the month, day, and year.
If you check Books Online, as I should have, you'd see that a number of the datepart arguments for which date part you want have variations. For example, the day can be coded as "d" or "dd". Similarly for Month. I knew this, and had assumed (bad, bad mistake) that year was the same. I coded the query, tested it, and didn't get an error, so I went with it.
Until we crossed the year live point and had an entry from the previous year for the same day.
The problem is with the line that says:
and datepart( y, entrydate) = datepart( y, getdate())
The "y" argument is actually for the day of the year, not the year. So when there was an entry for last year, the same month, same day, and same day of year, the wrong results are sent.
The fix? Simple enough, change the line to:
and datepart( yyyy, entrydate) = datepart( yyyy, getdate())
Conclusions
Not a great T-SQL example, but it definitely shows that bugs can creep into your code when you least expect it. This was an example of where even an experienced T-SQL programmer can make a mistake, have it slip through testing, but come back to bite you later. I'm just glad this was a small in-house application and not a piece of shrink wrapped software.
Note to everyone: Double check your code.
And ask MS for Intellisense in Query Analyzer :).
Steve Jones
©dkRanch.net February 2003
