Earlier today, one of our data guys asked me a SQL question regarding a dealers DMS. He was getting a weird issue reporting invalid labels. When I took a look, this was the offending bit of code:

DATE( ( DDATEY * 10000 ) + (DDATEM * 100) + DDATED, YYYYMMDD) > (current date - '180' days)

This looks odd, but it appears that they have split the date into 3 fields, and to do a query on a certain date range, you have to concatenate the date parts together, then do the conversion. The first thing I noticed about this was the '180'. Usually numeric values don’t have enclosures, so I removed those, and ran the code again. This time with a different result, but still an error. The error this time was invalid date.

When I started to think about the problem, I realized that the possible issue was in the concatenation of the date parts making an invalid date. So I started to break down the equation to figure out which part was causing the problem. I changed the query to:

DATE( ( DDATEY * 10000 ) + (DDATEM * 100) + '01', YYYYMMDD) > (current date - '180' days)

This would force every date combination to be the first of the month. This would seem logical to me, as I’d have assumed the day was more likely to be messed up rather than the month (30th february for example). This also generated an invalid date message. So this hinted the error was in another part of the date. To save running through all the date parts, I figured it’d be easier to split the date back up.

DDATEY >= YEAR(current_date) AND DDATEM >= MONTH(current_date - 2 months)

This probably resulted in giving them more information than they needed, but they can ignore what they don’t want. The only issue comes to the end of the year. When January rolls around, the above query will only return data for January, and not data back into the last month.

It only draws me to conclude that the front end validation has somewhere failed somewhere, and allowing bad data into the back end system. This makes the original query fail every time, as it’s a concatenated query, the entire table data is used, unless other limits are imposed first to drop the bad data.

This all makes me wonder, why on Earth would anybody use this split format? It’s difficult to produce reports on, without creating complicated queries. Take for example, trying to run a query in February of one year, for data back to April of the previous one. This ends up producing queries like:

DATE( ( DDATEY * 10000 ) + (DDATEM * 100) + DDATED, YYYYMMDD) > 20060401

Well, we know the above doesn’t work because of invalid dates… So we end up having to do this:

(DDATEY = 2006 and DDATEM >= 4) or (DDATEY = 2007)

This only gets more complicated the more expansive you want your date range to be. So why not just use a date format?

DATEFIELD > '2006-04-01'

Simple, and yet…. simple. I don’t see any benefits in trying to use the other methodology.

This is ammusing. The new energy saving bulbs could end up being more expensive than most people think. At least somebody found out the hard way anyway. Apparently the CFL bulbs that are being pushed (more like shoved) down our throats by companies like Walmart, and such, contain mercury. You know, the same stuff they used to put in thermometers until they figured out it was incredibly poisonous.

Hint of the day, don’t drop one. The $4.28 bulb could cost you in excess of $2,000 in cleanup costs alone.

New mastercard commerical anybody?

• New CFL bulb - $4.28
• Energy bill savings - $180
• Price of cleanup of toxic mercury due to dropped bulb - $2,000
• Price of knowing you’re saving the environment, and the cost of your own life… priceless

Why do Bill and Theresa rock? Because they found me these, and sent them down from Connecticut. They’re very similar to Jaffa Cakes, and quite tasty. They’ll be receiving the real things in September when I get back…

I wish this weather would shake up a bit. It’s usually starting to get pretty warm by now, and sunny by now. For the last few days it’s been in the low 70s, and very wet. This is all while the UK is showing an early summer… Something is terribly wrong there.

The Washington Post has an interesting story about an experiment they conducted at L’Enfant Plaza in January. The basic experiment was to find out how many people reacted to a world famous classical violinist playing at rush hour in the hub of Washington’s federal district. They have a handful of videos showing the effect. It’s actually an interesting article to read. They go on to interview some of the people that stopped, or didn’t stop, and a quick interview with the musical director of the National Symphany Orchestra. It was predicted a crowed would gather, and he’d easily make $150 in a short hour. The actual outcome is quite surprising, and most of his earnings came from a single person, who was the only person to recognise Bell¹.

Have you ever been in the mood where you want company, but want to be alone… at the same time?

A good friend is somebody that you can sit on a park bench for hours on end, not say a single word, and when it comes time to leave, know you have just had the best conversation

This quote sums up Adam entirely. We used to go bike riding late at night sometimes, along the sea front, all the way into Shoeberryness, not really talking, but when we got back, I always felt 100 times better than I had a few hours before, just like a burden had been lifted from my chest.

Neill, he was different. Somehow, I always felt comfortable talking to him about stuff, no matter how depressed I was. That also included 2am bike rides in freezing fog sometimes too. Crazy times.

Jodi, I think she could make me laugh no matter how bad things were. I’ll never forget ichy knees, and how they came about (don’t ask).

This nifty little device was mentioned over on Gizmodo today, as well as Scoble’s blog. Just watching the demo alone is pretty cool.

Something I’ve noticed around here, as I listen to somebody talking to a new hire, is the way we seem to be doing training, and discussions of our application. When I get asked what I do, or what my¹ company does, I talk about all the good stuff, and how we impact the companies we are deployed at.

However, I’ve noticed a shift when it comes to when people talk to new hires (or even prospective new hires) about stuff. I’ve noticed it starts off with a few good things, but as soon as it gets into an area of greyness, all the bad things about the system come flooding out. For example, when training a new helpdesk technician, the primary goal of the training should be an understanding of the system, how to perform all the basic operations, get data entered, etc. Once all these are covered, they should be given the chance to explore, and play about a bit, then given some further details into common issues we experience. The problem is, the training ususually starts on the login screen, a brief mention of the next page, and how to start entering data, and then the first common issue is mentioned, and then that’s where the training stops. The next few hours is spent discussing every little bit wrong with the system, and usually ends up in a bad comment, or two about other departments{^2].

This isn’t the only place this happens either. One of our other departments hired a new member of staff, and the similar kind of discussion came up. It started with a description of what the new person would be doing in the department, and a little background on the department, and application, then trailed off on a knee-jurk solution to a “maybe” problem, that has come up before, and went nowhere. This then spinned into a discussion about how we have certain issues on other parts of the application that make the departments job a little more complex.

Where I work, they do regular corporate “team building” excercises (which large corporation doesn’t?). We do one through a company called Senn-Delaney. One of the items that they try to nail home² is the importance of not “bad-mouthing” (edit: and prejudging) other departments. It generally has a bad result. This is a perfect example of what happens, as it ends up resulting in department a, not liking department b, without understanding anything on their side.

So now, we have new support guys, have a pre-programmed notion that our development department sucks. Now this is a pretty common conception at any company, mostly because the development guys usually don’t have to deal with the customers complaining about stuff, and their code usually works as originally designed, but not what the customer entirely wanted³. But I’m a firm believer in letting people uncover stuff on their own, especially when they are in the position of customer support, and should have some ability to explore problems themselves.

Apparently Kenneth Williams topped a survey of films’ funniest one-liners with his line form Carry on Cleo

Infamy! Infamy! They’ve all got it in for me!

More at The Register.

I couldn’t but help laugh at this when I saw it yesterday. Especially after one of the QA team was trying to clean out a wireless keyboard he found on a different desk.