Beware of the dates!

One of the hardest thing to handle when developing a solution are dates. You always end-up in some worst case scenario if you are not careful with them.




This week, it happened to me. Lucky we found the issue while executing a series of test plans, just in time before shipping into the production environment...



If you know about databases, than you know about SQL. We are using SQL Server and I had to modify a Stored Procedure where I needed to extract the last 2 digits of the current year. It's quite easy to do but nevertheless, I introduced a bug into our solution without knowing about it.




Select DATEPART(YYYY, Now())

The result is "2004"... So I looked for other format to get only the last 2 digits...

- YEAR = 2014
- YY = 2014
- Y = 14 ... Found it!

So I ended up coding "Select DATEPART(Y,Now())" to get the last 2 digits of the current year... Bad move!

I worked on the code on January 14th, 2014... My mistake was to assume that Y is a shorter version of YYYY.

On the 16th, while testing, I found out that the generated code was showing 16 instead of 14 for the current year. I was baffled as I had tested everything many times on the 14th and it was working perfectly. The thing is that "Y" is used to display the "day of the year", not the "last 2 digits of the year".

I would have done that script any other day in the year, and I would have realized that the value was the the year but something else. I had to do it exactly on the 14th of the year 2014...

There are two things to remember from this story:

1- Never assume without checking the documentation
2- Always re-test everything on pre-production environment

The solution I used was "Select right(convert(varchar,DATEPART(YY,NOW())),2)"





And now you know and knowing if half the battle! Yo, Joe!


Patrick Balleux