Pure T-SQL (Geo) Distance Calculations

Awesome post by Joe Finsterwald (http://www.wtfdeveloper.com/Default2.aspx) shows how to implement geographical distance calculation functionality in pure T-SQL.

T-SQL Epoch Date Conversion

I needed a way to convert a BIGINT containing a date in SSE (seconds since epoch - 1/1/1970) format into a DATETIME. The following query does the trick:

SELECT DATEADD(DAY, CAST(sse_value AS BIGINT)/86400000, CAST('19700101' AS DATETIME))

Thanks to some guy named Steve Kass for pointing this out on DevelopmentNow.

Calculating Work Days

I ran across the following T-SQL function to calculate the number of workdays between a given start date and end date. (This does not exclude non-weekend non-workdays such as holidays.)

Thanks to Jeff Moden for this function, which I found at http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/:

SELECT
  (DATEDIFF(dd, @StartDate, @EndDate) + 1)
 -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
 -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
 -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

T-SQL IP octet split

Found a neat piece of T-SQL that can be used to separate the octets of an IP address:

Declare @IP varchar(20)
Select @IP = '239.147.8.165'
select @IP AS IP,
cast(substring(@IP, 1, charindex('.', @IP) - 1) as int) AS Octet1,
cast(substring(@IP, charindex('.', @IP) + 1,
charindex('.', @IP, charindex('.', @IP) + 1) -
charindex('.', @IP) - 1) as int) as Octet2,
cast(reverse(substring(reverse(@IP), charindex('.', reverse(@IP)) + 1,
charindex('.', reverse(@IP), charindex('.', reverse(@IP)) + 1) -
charindex('.', reverse(@IP)) - 1)) as int) AS Octet3,
cast(reverse(substring(reverse(@IP), 1, charindex('.', reverse(@IP)) - 1)) as int) as Octet4

The source for this is http://www.umachandar.com/technical/SQL6x70Scripts/Main.htm.