Thursday, December 23, 2010

Merry Christmas, Sauron!





Friday, December 3, 2010

Roamatherapy!

Ahh, Christmas, the time for presents, tasty food, and ruminating on the memories of days when Santa Claus really existed outside mere childhood imagination.

For the cheapskate who's always wanted to dabble in aromatherapy, and because "road aromatherapy" or "road rage smells like Christmas" didn't seem catchy enough, I present ROAMATHERAPY


TRY THIS

  1. Go to your local live Christmas Tree seller. This might be your local supermarket with trees and wreaths out front, or some guy selling these out of a tent along the roadside.

  2. Grab a discarded sprig of Douglas Fir. You also could ask nicely, but I doubt the seller would mind if it's already fallen away from the tree.

  3. Using Duck™ tape, a chip clip, or a convenient crevice between the vent and the dash, secure the sprig so that hot air from the vents wafts across it.

  4. Turn on your car's heater. Akin to the principles used by various aromatherapy diffusers, the pleasant aroma of the Douglas Fir is expressed from the plant material into your car's interior.

 

If your memories of Christmas are gloomy or nonexistent, hopefully this will bring you some of the Christmas cheer I associate with the holiday that is far more enjoyable than its rampant consumerism.


 

Thursday, December 2, 2010

String Concatenation and Implicit Conversions

I needed to modify a SQL function to strip some leading zeros in order to concatenate a string. Part of this process involved dealing with pluralization of the number of cents (I'm picky that way).


SELECT
 CASE
  WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) = 1 THEN ''
  WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) > 1 THEN 's' 
  ELSE ''
 END
 

The idea is that if the value (0.30 representing cents) translates into a number greater than 1, the resulting concatenated sentence will read something like "The discount is 30 cents." If the value equals 1, however, it would read "The discount is 1 cent."

When the above is executed, I get an error:

 Msg 245, Level 16, State 1, Line 1
 Conversion failed when converting the varchar value '0.03' to data type int.



At first this baffled me. Data type int?? I wasn't trying to obtain an integer value, I was ultimately trying to obtain a varchar for this string I'm building.

Then it hit me, SQL is performing an implicit conversion when it is comparing the value resulting from the conversion first to decimal, then float, and finally to varchar, with 1.

Lesson learned, when attempting to compare two varchar with numeric values, make sure that if the value being tested is decimal, that the static value being used to compare it against is also decimal.

I made the following changes and now it works flawlessly.


SELECT
 CASE
  WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) = 1.0 THEN ''
  WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) > 1.0 THEN 's' 
  ELSE ''
 END