Peter DeBetta posted a while back with a function to take a hex string and convert it to varbinary It has a couple of slight issues, the biggest of which is it can’t handle an odd number of hex digits. Below is my replacement, because it’s using bigints the upper limit isn’t as high, but it’s good enough for most things:

CREATE FUNCTION dbo.HexStrToVarBinary(@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN
    DECLARE @hex char(1), @i int, @place bigint, @a bigint
    SET @i = LEN(@hexstr) 

    set @place = convert(bigint,1)
    SET @a = convert(bigint, 0)

    WHILE (@i > 0 AND (substring(@hexstr, @i, 1) like ‘[0-9A-Fa-f]’))
     BEGIN
        SET @hex = SUBSTRING(@hexstr, @i, 1)
        SET @a = @a +
    convert(bigint, CASE WHEN @hex LIKE ‘[0-9]’
         THEN CAST(@hex as int)
         ELSE CAST(ASCII(UPPER(@hex))-55 as int) end * @place)
    set @place = @place * convert(bigint,16)
        SET @i = @i - 1

     END 

    RETURN convert(varbinary(8000),@a)
END
GO

Carpe Datum over at MSDN talks about people wanting Query Analyzer back over Management Studio.  I admit, the lack of Query Analyzer has been a source of ire for me as well.  I like the default Management Studio for editing databases and what not, but if I want to whip up a quick query or something I miss query analyzer.  Unfortunately the solution he provides causes Management Studio to always assume you want to run a query.  After a bit of fooling around, I came up with a batch routine that seems to mimic Query Analyzer pretty well.  I present to you isqlw.bat :)

@echo offif  ‘%1′==” goto nofilegoto file:nofileecho. > %TEMP%\Untitled.sqlstart sqlwb -nosplash %TEMP%\Untitled.sqlgoto end:filestart sqlwb -nosplash %1:end

This works well for me, YMMV of course, but I hope you might find it useful.

So, at work I’m having the fun experience of having to write a job scheduling app, using MSSQL as the back end. One of the things I had to figure out was how to determine of a day was, say, the first Monday of the month or the third Thursday or so forth. I couldn’t find any built in functions that did this, so I had to write one. Behold:

Create Function fn_ReturnOrdinalDay( @TestDate datetime)returns intbegindeclare@ordinal int,@loopdate datetime

set @loopdate = @TestDateset @ordinal = 0while datepart(m, @TestDate) = datepart(m, @loopdate)beginset @ordinal = @ordinal + 1set @testdate = dateadd(d, -7, @testdate)end

return @ordinalend

When called like fn_ReturnOrdinalDay(getdate()) will return 1 - 5 depending on how many times that day has occured. For instance for today (Monday June 18th, 2007) it would return 3 since this is the Third Monday of the Month.