My photo
Auckland, New Zealand
Smurf sized geeky person with a penchant for IT, gaming, music and books. Half of industrial duo 'the craze jones'. Loves data, learning new things, teaching new things and being enthusiastic.

Friday, 30 April 2010

Strange goings on in SQL land

Weird things with dates

I have a simple sql script that extracts raw data from a quality centre database and extracts it to an excel spreadsheet, part of this is shown below:

SELECT bg_bug_id as DefectID,
bg_detection_date as RawDetectionDate,
convert(varchar(10), bg_detection_date, 103) as FormatDetDate,
bg_closing_date as RawClosingDate,
convert(varchar(10), bg_closing_date, 103) as FormatCloseDate
FROM BUG

The issue I'm having is with the formatted dates. Some come through correctly and are left justified with the correct date, the others come through in US format and are right justified. When looking at the excel cell format, the left justified correct dates are showing as General format, and the incorrect right justified dates are Date format.

Yes, I could just do the formatting on the RawDate in Excel which guarantees the correct date, and yes I could do some fancy malarky casting to a string and then back to a date but that would affect performance, so it's solvable, but why is it doing this in the first place? It's bugging the hell out me.

I've tried various things so far, including casting, converting, floats etc... and the most reliable so far is:

CONVERT (varchar(11), convert (DATETIME, BUG.BG_DETECTION_DATE, 103))

However, this is returning the dates in the format 'Mar 23 2010' which isn't really what I want but will do for now.

So what's going on? Any ideas?

Wednesday, 28 April 2010

Logic FAIL: Monty Hall Problem – GAH!

Now those of you who know me well will know that I love maths, I can spend hours and hours playing around with equations and figures and formulae, however a friend (http://rocko.co.nr/) told me at the weekend about the Monty Hall Problem and this one got me right in the logic chip. My first reaction to this was ‘that can’t be right, it doesn’t make mathematical sense’. It is a very counter intuitive problem and one that has caused much debate over the years if my googling on the subject is anything to go by.

The problem for those of you who don’t know it:
"Suppose you're on a game show and you're given the choice of three doors: Behind one door is a car; behind the others, goats. You pick a door, say No. 1, and the host, who knows what's behind the other doors, opens another door, say No. 3, which has a goat. He then says to you, 'Do you want to pick door No. 2?' Is it to your advantage to take the switch?” Ref: http://math.ucsd.edu/~crypto/Monty/montybg.html

My first thoughts were, well of course it doesn’t matter. Initially you have a 1 in 3 chance of picking the right door and once one door has been removed you have a 1 in 2 chance of the door you’ve chosen being correct, so it doesn’t matter if you switch doors. But my friend insisted that no, you now had a 2 in 3 chance of being correct. So I’ve been playing with the maths and doing some reading up on this to find out why.

It seems that one of the biggest factors in why the odds of 2 in 3 is correct is that the game show host knows which door the car is behind, so he will always accurately remove a door without a prize behind it. If the game show host did NOT know what was behind each door, then my original thinking that you now have a 1 in 2 chance of winning would have been correct, phew, the logic chip isn't as flawed as first thought. I hadn’t been taking into account the game show host. Silly me!

Someone has already tested the problem using a little application they’ve written that knows which goatish door to remove. The application resulted in ~66% win rate for the switched door, his detailed notes can be found here: http://skepticblog.org/2009/03/10/monty-hall-problem-put-to-the-test/. So now the alternate theory of the application NOT knowing which door is a goat door needs to be tested to see what happens to the figures. Feel free to knock up an app to do this, if I get to it first then I’ll post my results on here.

Monday, 12 April 2010

SQL Server 2008 Certifications update

SQL Saturday in Auckland went really well on Saturday. Huge thank you to everyone who attended and especially to Dave Dustin for pulling it all together.

On Saturday I re-presented the 15 minute certifications talk. As the last time this was presented was November 2008, there were quite a few changes. The ppt slides have been updated to reflect the changes since Nov 2008 and the latest version has been uploaded.

Let me know if you have any questions on any of the presentations or white papers.

Wednesday, 7 April 2010

Shiny Things

One of the hardest things for me as a geek is forcing myself to walk past shiny new games with large pink SPECIAL OFFER PRICE stickers on them. Please supermarkets, don’t put cut price games on the way to checkouts where you can ensnare sun-starved and fragile geeks.

Point in question, yesterday I went into the big red shed to buy a printer for the office and only through sheer willpower managed to come out without Wii Fit Plus, C&C 4 and the new God of War game. I’m getting better at walking past these evil stands of money grabbing games, though yesterday I did pick up C&C 4 and start walking towards the checkout before thinking twice. I stopped myself, turned round taking out some teenagers with my umbrella (quite by accident – it was a comic turn the Marx brothers would have been proud of) and put the game back on the shelf. If I’d bought it there would have been that wonderful rush of instant gratification that comes from buying something new and shiny but I know that once I got home the guilt and self reprisals would have started, well, that and convincing myself that now I have a new shiny game I really, REALLY need a brand spanking new graphics card to go with it and just a liddle bit more memory.

PC Upgrades are a slippery slope. First it’s the top of the range, mega-spiffing graphics card with more on-board memory than most work PCs have as RAM, then it’s the RAM upgrade from the measly 4GB to the maximum possible amount that you can cram into your PC, which in my case is rather a lot; 4GB corsairs x 6 slots = nice! Gotta love those motherboards with loadsa RAM slots. Then you start to look at the quad core i7 and wonder if there is possibly a faster version you could sneak into the machine, you trawl through blog posts and processor reviews and pre-order the next best thing then wait on tenterhooks for it to arrive, and when it does you bounce around the house like Tigger on acid and the office ends up looking like a PC factory with parts lying around everywhere. Ahhhh.... happy days....