• aaron.reese (8/22/2013)


    Love it 😛

    Reminds me of one of my favourite jokes:

    Q: Why does Excel get Christmas and Halloween confused?

    A: OCT31 = DEC25 (actually OCT2DEC(31) = 25 but it doesn't sound so good when you write it like that.

    The general rule with Excel always seems to be don't trust it unless Excel created it in the first place.

    I have a CSV file I need to bring into SQL occasionally and it contains product codes. The two which always confuse Excel are "012" which it sees as 12 (Apparently it knows better than me that numbers don't start with a leading zero so it kindly removes them to save me the trouble) and bizarrely "2E2" which it assumes I meant two Exponent two so saves as 200 2E2 couldn't possibly be a string, it is a valid numerical notation even though the other 3000 cells in the column are strings.

    It does that a lot with zip codes for me. It also does a bang up job of converting any foreign number that starts with 0 into exponential notation, from where there is no return. It's especially fond of doing that to UK numbers, for some reason.

    Many people, including my boss, seem to think that this will fix it:

    update table set columntofix= convert(numeric(38,0),cast(columntofix as float))

    But it usually just gives me a close but no cigar number that has the last three digits rounded up. So, if a phone number ends in 9679, it will be 9700.