T-SQL Hex String to VarBinary (Improved)

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 

Query Analyzer, Resurrected.

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 off
if  '%1'=='' goto nofile
goto file
:nofile
echo. > %TEMP%\Untitled.sql
start sqlwb -nosplash %TEMP%\Untitled.sql
goto end
:file
start sqlwb -nosplash %1
:end

 

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

Determining the Ordinal of A Weekday.

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 int
begin
declare
@ordinal int,
@loopdate datetime

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

return @ordinal
end

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.