death to sql server (part 1)

Just for a background .. anyone who has talked to me for more than 30 seconds about databases knows that I hate Microsoft SQL Server with a passion. The real bonus is that it’s not just an unfounded passion! I have proof of its inadequacy, which you’ll see in this special 5,789-part story.

In today’s adventure our hero ran into a small problem — he wanted to select year-month-day from the database in the format of (2006-01-17). Easy, you would think, until you’ve actually worked with the database first hand.

First, I tried selecting them all individuall and concatenating them together .. which worked, except that datepart() won’t pad the zeroes (it would return 2006-1-17).

Next, I decided to write my own UDF (User Defined Function for the unwashed masses), which would set the variables, but I ran into another problem — you can’t run getdate() within a UDF. That’s weird (translation: That’s about the dumbest thing I’ve seen this databse do yet).

Actually, the last problem turned out for a small bonus, because with my new UDF I could just pass it in as a variable anyway. Sure it complicates the function a little bit, but it will work out in the end.

Speaking of which, here is the final SQL for the function:

CREATE FUNCTION dateYMD(@getdate DATETIME) RETURNS VARCHAR(255) AS
BEGIN
DECLARE @year CHAR(4);
DECLARE @month VARCHAR(2);
DECLARE @day VARCHAR(2);
DECLARE @date VARCHAR(255);

SET @year = YEAR(@getdate);
SET @month = MONTH(@getdate);
SET @day = DAY(@getdate);

IF LEN(@day) = 1 BEGIN SET @day = '0' + @day END
IF LEN(@month) = 1 BEGIN SET @month = '0' + @month END

SET @date = @year + '-' + @month + '-' + @day;

RETURN @date;

END

Then, the final blow. After about getting halfway through writing this one, I find that there’s an undcoumented feature (I guess those Microsoft developers enjoy Easter Eggs just as much as I do) to do the same thing I was doing:

SELECT CONVERT(VARCHAR, GETDATE(), 23);

SQL Server: 1, Developer: 0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s