Patron Saint of Lost Yaks

## ISO week calculation for all years 1-9999 without dependencies

CREATE FUNCTION dbo.fnISOWEEK ( @Year SMALLINT, @Month TINYINT, @Day TINYINT ) RETURNS TINYINT AS BEGIN RETURN ( SELECT CASE WHEN nextYearStart <= theDate THEN 0 WHEN currYearStart <= theDate THEN (theDate - currYearStart) / 7 ELSE (theDate - prevYearStart) / 7 END + 1 FROM ( SELECT (currJan4 - 365 - prevLeapYear) / 7 * 7 AS prevYearStart, currJan4 / 7 * 7 AS currYearStart, (currJan4 + 365 + currLeapYear) / 7 * 7 AS nextYearStart, CASE @Month WHEN 1 THEN @Day WHEN 2 THEN 31 + @Day WHEN 3 THEN 59 + @Day + currLeapYear WHEN 4 THEN 90 + @Day + currLeapYear WHEN 5 THEN 120 + @Day + currLeapYear WHEN 6 THEN 151 + @Day + currLeapYear WHEN 7 THEN 181 + @Day + currLeapYear WHEN 8 THEN 212 + @Day + currLeapYear WHEN 9 THEN 243 + @Day + currLeapYear WHEN 10 THEN 273 + @Day + currLeapYear WHEN 11 THEN 304 + @Day + currLeapYear WHEN 12 THEN 334 + @Day + currLeapYear END + currJan4 - 4 AS theDate FROM ( SELECT CASE WHEN (@Year - 1) % 400 = 0 THEN 1 WHEN (@Year - 1) % 100 = 0 THEN 0 WHEN (@Year - 1) % 4 = 0 THEN 1 ELSE 0 END AS prevLeapYear, CASE WHEN @Year % 400 = 0 THEN 1 WHEN @Year % 100 = 0 THEN 0 WHEN @Year % 4 = 0 THEN 1 ELSE 0 END AS currLeapYear, 365 * (@Year - 1) + (@Year - 1) / 400 - (@Year - 1) / 100 + (@Year - 1) / 4 + 3 AS currJan4 WHERE @Year BETWEEN 0 AND 9999 AND @Month BETWEEN 1 AND 12 AND @Day >= 1 AND 1 = CASE WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) AND @Day <= 31 THEN 1 WHEN @Month IN (4, 6, 9, 11) AND @Day <= 30 THEN 1 ELSE 0 END ) AS d WHERE CASE WHEN currLeapYear = 1 AND @Day <= 29 THEN 1 WHEN @Day <= 28 THEN 1 ELSE 0 END = 1 Read more →

## How to calculate the covariance in T-SQL

DECLARE @Sample TABLE ( x INT NOT NULL, y INT NOT NULL ) INSERT@Sample VALUES (3, 9), (2, 7), (4, 12), (5, 15), (6, 17) ;WITHcteSource(x, xAvg, y, yAvg, n) AS ( SELECT 1E * x, AVG(1E * x) OVER (PARTITION BY (SELECT NULL)), 1E * y, AVG(1E * y) OVER (PARTITION BY (SELECT NULL)), COUNT(*) OVER (PARTITION BY (SELECT NULL)) FROM @Sample ) SELECTSUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)] FROMcteSource Legacy Comments dmSQL 2012-10-24 re: How to calculate the covariance in T-SQL Thanks. Read more →

## Convert UTF-8 string to ANSI

CREATEFUNCTION dbo.fnConvertUtf8Ansi ( @Source VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE@Value SMALLINT = 160, @Utf8 CHAR(2), @Ansi CHAR(1) IF @Source NOT LIKE '%[ÂÃ]%' RETURN@Source WHILE @Value <= 255 BEGIN SELECT@Utf8 =CASE WHEN @Value BETWEEN 160 AND 191 THEN CHAR(194) + CHAR(@Value) WHEN @Value BETWEEN 192 AND 255 THEN CHAR(195) + CHAR(@Value - 64) ELSE NULL END, @Ansi = CHAR(@Value) WHILE CHARINDEX(@Source, @Utf8) > 0 SET@Source = REPLACE(@Source, @Utf8, @Ansi) SET@Value += 1 END RETURN@Source END Read more →

## A tale from a Stalker

Today I thought I should write something about a stalker I've got. Don't get me wrong, I have way more fans than stalkers, but this stalker is particular persistent towards me. Read more →

## Simple function to get beginning or end of month

CREATEFUNCTION dbo.fnIsOnMonthEdge ( @theDate DATETIME ) RETURNSSMALLINT AS BEGIN RETURN CASE @theDate WHEN '99991231' THEN 1 ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, 1, @theDate)) END + CASE @theDate WHEN '17530101' THEN -1 ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, -1, @theDate)) END END   Read more →

## Unified Relational Division algorithm

Finally! Today I finished my presentation about finding a unified algorithm for Relational Division, which should work for all types of division; singlerecord and multirecord, singlecolumn and multicolumn and both exact division and with remainder. Read more →

## The internal storage of a DATETIMEOFFSET value

Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes. Read more →

## The internal storage of a DATETIME2 value

Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes. Read more →

## The internal storage of a SMALLDATETIME value

SELECT[Now], BinaryFormat, SUBSTRING(BinaryFormat, 1, 2) AS DayPart, SUBSTRING(BinaryFormat, 3, 2) AS TimePart, CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT) AS [Days], DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT), 0) AS [Today], SUBSTRING(BinaryFormat, 3, 2) AS [Ticks], DATEADD(MINUTE, CAST(SUBSTRING(BinaryFormat, 3, 2) AS SMALLINT), 0) AS Peso FROM ( SELECT CAST(GETDATE() AS SMALLDATETIME) AS [Now], CAST(CAST(GETDATE() AS SMALLDATETIME) AS BINARY(4)) AS BinaryFormat )AS d   Legacy Comments Rob Volk 2010-12-15 re: The internal storage of a SMALLDATETIME value Have you done this for the datetime2 types? Read more →

## The internal storage of a DATETIME value

SELECT [Now], BinaryFormat, SUBSTRING(BinaryFormat, 1, 4) AS DayPart, SUBSTRING(BinaryFormat, 5, 4) AS TimePart, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT) AS [Days], DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT), 0) AS [Today], CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT) AS [Ticks], DATEADD(MILLISECOND, 1000. Read more →

## Sweet and simple "first and last" weekday calculation

DECLARE @Year SMALLINT = 2011, @NumberOfYears TINYINT = 3 ;WITHcteCalendar(FirstOfMonth, LastOfMonth) AS ( SELECT DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth, DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth FROM master. Read more →

## How to determine if you should use full or differential backup?

Or ask yourself, "How much of the database has changed since last backup?". Here is a simple script that will tell you how much (in percent) have changed in the database since last backup. Read more →

## Fast easter day function

CREATEFUNCTION dbo.fnGetEasterDate ( @Year SMALLINT ) RETURNSDATE AS BEGIN RETURN ( SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CONVERT(DATETIME, CAST(@Year AS CHAR(4)) + BaseDate, 112)) / 7 * 7, 6) FROM ( SELECT CASE @Year % 19 WHEN 0 THEN '0415' WHEN 1 THEN '0404' WHEN 2 THEN '0324' WHEN 3 THEN '0412' WHEN 4 THEN '0401' WHEN 5 THEN '0419' WHEN 6 THEN '0409' WHEN 7 THEN '0329' WHEN 8 THEN '0417' WHEN 9 THEN '0406' WHEN 10 THEN '0326' WHEN 11 THEN '0414' WHEN 12 THEN '0403' WHEN 13 THEN '0323' WHEN 14 THEN '0411' WHEN 15 THEN '0331' WHEN 16 THEN '0418' WHEN 17 THEN '0408' WHEN 18 THEN '0328' ELSE NULL END WHERE @Year BETWEEN 1900 AND 9999 )AS d(BaseDate) ) END Legacy Comments iStan 2011-01-17 re: Fast easter day function Hi, Read more →

## Excel XIRR function

First create a table type like this CREATE TYPE dbo.MyXirrTable AS TABLE ( theValue DECIMAL(19, 9) NOT NULL, theDate DATETIME NOT NULL ) GO And then you create a function like this CREATE FUNCTION dbo. Read more →

## CHECKSUM weakness explained

The built-in CHECKUM function in SQL Server is built on a series of 4 bit left rotational xor operations. See here in a previous forum post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832 for more explanation. Today, I wanted to see how often a collision (or false positive) can occur. Read more →

## Joe Celko's Puzzles and Answers - The Restaurant seat assignment Problem

This problem is designed to come up with a solution that uses the smallest amount of storage possible for a 1,000 seat restaurant. I've come up with a solution that need only 125 bytes of storage. Read more →

## String concatenation and entitization

This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog. Consider this sample data DECLARE@Sample TABLE ( ID INT, Data VARCHAR(100) ) INSERT@Sample VALUES (1, 'Peso & Performance SQL'), (1, 'MVP'), (2, 'Need help <? Read more →