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