Category Archives: Uncategorized

MS SQL Server Time Dimension UDF

I use this function when writing reports.

Use:

SELECT
  time.Date
  , Count(tab.ID) AS [Count]
FROM
  dbo.TimeDimension('1/1/2007 00:00:00', '12/31/2007 23:59:59', 'month', 1) time
  LEFT OUTER JOIN
   TempTable tab
  ON tab.DateCreated BETWEEN time.Date AND time.DateEnd

Here is the function. I don’t know if there is a better way to do this, if there is can someone tell me. As far as I know I am the only person to post a function like this.

CREATE FUNCTION [dbo].[TimeDimension] (
 @DateStart datetime
 , @DateEnd datetime
 , @Type varchar(20)
 , @IncrementValue int
)
RETURNS @Dates TABLE ( Date datetime , DateEnd datetime )
AS
BEGIN
 DECLARE @DateCurrent datetime, @NextDate datetime
 SET @DateCurrent = @DateStart

 WHILE (@DateCurrent <= @DateEnd)
 BEGIN
 SET @NextDate = CASE (@Type)
 WHEN 'year' THEN DateAdd(year, @IncrementValue, @DateCurrent)
 WHEN 'quarter' THEN DateAdd(quarter, @IncrementValue, @DateCurrent)
 WHEN 'month' THEN DateAdd(month, @IncrementValue, @DateCurrent)
 WHEN 'dayofyear' THEN DateAdd(dayofyear, @IncrementValue, @DateCurrent)
 WHEN 'day' THEN DateAdd(day, @IncrementValue, @DateCurrent)
 WHEN 'week' THEN DateAdd(week, @IncrementValue, @DateCurrent)
 WHEN 'weekday' THEN DateAdd(weekday, @IncrementValue, @DateCurrent)
 WHEN 'hour' THEN DateAdd(hour, @IncrementValue, @DateCurrent)
 WHEN 'minute' THEN DateAdd(minute, @IncrementValue, @DateCurrent)
 WHEN 'second' THEN DateAdd(second, @IncrementValue, @DateCurrent)
 WHEN 'millisecond' THEN DateAdd(millisecond, @IncrementValue, @DateCurrent)
 END

 INSERT INTO @Dates ( Date , DateEnd )
 VALUES ( @DateCurrent , DateAdd(second, -1, @NextDate) )

 SET @DateCurrent = @NextDate
 END
 RETURN
END

MD5 Hash in SQL Server 2000 and 2005, 32 bit and 64 bit

SQL Server 2000
xp_MD5 at http://www.codeproject.com/database/xp_md5.asp

Also works in SQL Server 2005 32 bit

SQL Server 2005

DECLARE @Data varchar(MAX)
SubString(master.dbo.fn_varbintohexstr(HashBytes('MD5', @Data)), 3, 32)

Works in SQL Server 2005 32 and 64 bit

Solution found at http://www.codeproject.com/database/xp_md5.asp?df=100&forumid=32460&select=1519990#xx1519990xx

My Introduction

Hello there, this is my first blog at tylerclendenin.com.

To start let me tell you a little about myself.

What do I hope to accomplish with this blog you ask?

  • To become a part of the global blog community.
  • To have a place on the internet that not only represents my views but also challenges other to think more about the world around them.
  • To write articles describing things that I have to overcome that I could not find another resource on the internet to help me. Maybe I can help someone else in that regard.