Below is a handy function for converting a hexadecimal value into an integer using a SQL Server user defined function.

IF OBJECT_ID('dbo.udfHex2Int') IS NOT NULL
        DROP FUNCTION dbo.udfHex2Int
GO
CREATE FUNCTION dbo.udfHex2Int
(
  @hexstr AS varchar(1000)
)
-- Function converts VARCHAR representation of HEX to INT
-- 'FF'  --> 255

RETURNS INT
AS
BEGIN

  IF @hexstr IS NULL RETURN NULL

  DECLARE
    @curbyte AS int,
    @varbin  AS varbinary(500)

  IF @hexstr LIKE '0x%' SET @hexstr = SUBSTRING(@hexstr, 3, 8000)

  SET @hexstr =
    CASE LEN(@hexstr) % 2 WHEN 1 THEN '0' ELSE '' END + @hexstr

  SET @varbin = 0x
  SET @curbyte = LEN(@hexstr) / 2

  WHILE @curbyte > 0
  BEGIN
    SET @varbin =
      CAST(
        CASE SUBSTRING(@hexstr, @curbyte * 2, 1)
          WHEN '0' THEN 0x00
          WHEN '1' THEN 0x01
          WHEN '2' THEN 0x02
          WHEN '3' THEN 0x03
          WHEN '4' THEN 0x04
          WHEN '5' THEN 0x05
          WHEN '6' THEN 0x06
          WHEN '7' THEN 0x07
          WHEN '8' THEN 0x08
          WHEN '9' THEN 0x09
          WHEN 'A' THEN 0x0A
          WHEN 'B' THEN 0x0B
          WHEN 'C' THEN 0x0C
          WHEN 'D' THEN 0x0D
          WHEN 'E' THEN 0x0E
          WHEN 'F' THEN 0x0F
        END |
        CAST(
          CASE SUBSTRING(@hexstr, @curbyte * 2 - 1, 1)
            WHEN '0' THEN 0x00
            WHEN '1' THEN 0x10
            WHEN '2' THEN 0x20
            WHEN '3' THEN 0x30
            WHEN '4' THEN 0x40
            WHEN '5' THEN 0x50
            WHEN '6' THEN 0x60
            WHEN '7' THEN 0x70
            WHEN '8' THEN 0x80
            WHEN '9' THEN 0x90
            WHEN 'A' THEN 0xA0
            WHEN 'B' THEN 0xB0
            WHEN 'C' THEN 0xC0
            WHEN 'D' THEN 0xD0
            WHEN 'E' THEN 0xE0
            WHEN 'F' THEN 0xF0
          END AS tinyint) AS binary(1))
      + @varbin
    SET @curbyte = @curbyte - 1
  END

  RETURN CAST(@varbin AS INT)

END 

Labels: Hexadecimal, SQL, UDF

When creating a report often the date column provides more detail than we need to see. Here is a handy little UDF that rounds a DATETIME column to either the MINUTE, HOUR, DAY, WEEK or MONTH.

DateFirst UDF

IF OBJECT_ID('dbo.udfDateFirst') IS NOT NULL
    DROP FUNCTION dbo.udfDateFirst
GO
CREATE FUNCTION dbo.udfDateFirst
(
    @interval    VARCHAR(10),
    @rawDate    DATETIME
)
RETURNS DATETIME
AS
BEGIN    
    -- Remove seconds for allintervals
    SET @rawDate = CAST(@rawDate AS SMALLDATETIME)

    IF @interval = 'MINUTE'        
        RETURN @rawDate
    ELSE -- strip off minutes
        SET @rawDate = DATEADD(minute,-1 * DATEPART(minute,@rawDate),@rawDate)

    IF @interval = 'HOUR'        
        RETURN @rawDate
    ELSE -- strip off hours
        SET @rawDate = DATEADD(hour, -1 * DATEPART(hour,@rawDate),@rawDate)

    IF @interval = 'DAY'
        RETURN @rawDate

    IF @interval = 'WEEK'
        RETURN DATEADD(day, -1 * DATEPART(weekday,@rawDate),@rawDate)

    -- move date to first of month
    SET @rawDate = DATEADD(day, -1 * DATEPART(day,@rawDate) + 1,@rawDate)

    IF @interval = 'MONTH'
        RETURN @rawDate
    
    -- move Date to first of year
    SET @rawDate = DATEADD(month, -1 * DATEPART(month,@rawDate)+1 , @rawDate)

    RETURN @rawDate
    
END

Test Query

DECLARE @now AS DATETIME
SET @now = getDate()

SELECT dbo.udfDateFirst('MINUTE',@now)
SELECT dbo.udfDateFirst('HOUR',@now)
SELECT dbo.udfDateFirst('DAY',@now)
SELECT dbo.udfDateFirst('WEEK',@now)
SELECT dbo.udfDateFirst('MONTH',@now)
SELECT dbo.udfDateFirst('YEAR',@now)

Labels: Dates, SQL, UDF

This article is written for SQL Server 2000, which supports user-defined functions and OUTER JOINS. All examples come from the Northwind database which is part of the default installation of SQL Server.

In SQL an OUTER JOIN is where you want to return all the elements in a table regardless of whether they exist in the second table. The most common case for using an OUTER JOIN is when you need a COUNT or SUM value even when the aggregate function returns a zero. Before we get to the heart of this article, let me use two queries to demonstrate the power of a basic OUTER JOIN.

Customers and Order COUNT

There are 2 ways to tackle this request. The first is with an INNER JOIN. It will only return Customers that have at least one order. The second way is with an OUTER JOIN. It will return all Customers, even those with no Orders.

-- A: INNER JOIN (returns 89 customers, all of which have at least 1 order) 
SELECT C.CompanyName, COUNT(R.OrderID) AS OrderCount 
FROM Customers C 
INNER JOIN Orders R ON C.customerID = R.customerID 
GROUP BY C.CompanyName 
ORDER BY COUNT(R.OrderID) DESC 

-- B: OUTER JOIN (returns all 91 customers, 2 of which have 0 orders) 
SELECT C.CompanyName, COUNT(R.OrderID) AS OrderCount 
FROM Customers C 
LEFT OUTER JOIN Orders R ON C.customerID = R.customerID 
GROUP BY C.CompanyName 
ORDER BY COUNT(R.OrderID) DESC 

Orders by Day of the Week for July 1996

The first OUTER JOIN was straight-forward. But what if the entity you need to perform an OUTER JOIN upon doesn't exist in the database? One such entity that comes to mind is date. Let's suppose the sales manager is trying to determine which day she is going to paint the office based upon the day of the week with historically the least amount of orders. The least amount could be zero, so we need to perform an OUTER JOIN. But how can you perform an OUTER JOIN against a range of dates? Three ways come to mind.

1. Create a TABLE of dates (or digits) using a SQL statement. This could be a temporary or permanent table.
2. Get verbose with your SQL and write long CASE statements.
3. Use the 2 user-defined functions I'm providing to you in this article.

The problem with #1 is now you're executing multiple statements to handle one query. You are creating a TABLE, perform the INSERT and ultimately dropping the TABLE. At this point you'll consider writing a stored procedure to accomplish the task. All for a single query. Too much work. The problem with #2 is your SQL can get very long and hard to read and the data is returned more like an Access PIVOT TABLE. The slick way to handle this task is using a user-defined function.

-- C: Using multiple CASE and SUM statement (long, ugly, and tabular) 
SELECT SUM(CASE DATEPART(dw,OrderDate) WHEN 0 THEN 1 ELSE 0 END) AS MonSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 1 THEN 1 ELSE 0 END) AS TueSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 2 THEN 1 ELSE 0 END) AS WedSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 3 THEN 1 ELSE 0 END) AS ThuSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 4 THEN 1 ELSE 0 END) AS FriSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 5 THEN 1 ELSE 0 END) AS SatSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 6 THEN 1 ELSE 0 END) AS SunSales 
FROM Orders 
WHERE OrderDate BETWEEN '7/1/1996' AND '8/1/1996' 

-- D: Performing an OUTER JOIN against a user-defined function (dbo.udfNumbers) 
SELECT DATENAME(dw,N.number) AS DayName, COUNT(R.orderID) AS OrderCount 
FROM dbo.udfNumbers(0,6) N 
LEFT OUTER JOIN Orders R ON N.number = DATEPART(dw,R.OrderDate) 
AND R.OrderDate BETWEEN '7/1/1996' AND '8/1/1996' 
GROUP BY DATENAME(dw,N.number), N.Number 
ORDER BY N.number

Using dbo.udfNumbers

The dbo.udfNumbers user-defined function returns a TABLE of numbers. It is available for download at the end of this article. The upper range limit is 999,999. If you need larger numbers, modifying the function should be fairly easy.

-- E: Usage dbo.udfNumbers(low range,high range) 
-- will return 6,7,8,9,10,11,12
SELECT number FROM dbo.udfNumbers(6,12)

Orders by Date for July 1996

I use dbo.udfNumbers for those quick queries where I just need a range of digits. For most date-based reporting I use the far more powerful dbo.udfDateTimes. This user-defined function uses the digit logic in dbo.udfNumbers along with SQL Server's date and time function to return ranges of datetime. Query C was based upon Day of the Week, so there were only 7 possible values. What if the sales manager asked for a day by day breakdown of orders for July? That would turn into one long and ugly SQL statement. The dbo.udfDateTimes function creates the date range need for that OUTER JOIN without the long and ugly SQL.

-- F: dbo.udfDateTimes day interval for July 1996 
SELECT dtime AS OrderDate, COUNT(R.orderID) AS OrderCount 
FROM dbo.udfDateTimes ('7/1/1996', '7/31/1996',1,'day') DT 
LEFT OUTER JOIN Orders R ON DT.dtime = R.OrderDate 
GROUP BY dtime

Using dbo.udfDateTimes

Like dbo.udfNumbers, the user-defined function dbo.udfDateTimes returns a TABLE. The first parameter is start date. The second is end date. The third parameter is interval. In most cases this will be set to 1, but if you had a need to see one of the dateparts at an alternate interval, you would modify this parameter. The last parameter is datepart. Those supported are year, quarter, month, week, day, hour and minute. The upper range is 9,999,999, which (like udfNumbers) can easily be modified should need a function that returns more datetimes.

Download

dbo.udfNumbers - function that returns a table of digits.
dbo.udfDateTimes - function that returns a table of datetimes.

Last Words

One last piece of advice is to get your datetime range correct first before attempting to OUTER JOIN with it. Only once you know the dbo.udfDateTimes is returning the datetimes you need for the query should you proceed with your OUTER JOIN. Although the Northwind database deals with orders, another great use for these functions is when looking for missing data. In those cases where you need to seek out the datetimes where no data is present, add a HAVING clause set to zero.

-- G: Days in July 1996 with no orders 
SELECT dtime AS OrderDate, COUNT(R.orderID) AS OrderCount 
FROM dbo.udfDateTimes ('7/1/1996', '7/31/1996',1,'day') DT 
LEFT OUTER JOIN Orders R ON DT.dtime = R.OrderDate 
GROUP BY dtime 
HAVING COUNT(R.orderID) = 0

Labels: Dates, SQL, UDF