Peter Larsson Blog

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 →

Remove all Extended Properties in a database

During my tests to port several databases to SQL Azure, one of the recurring things that fails export is the Extended Properties. So I just wanted to remove them. This is a simple wayh to list all Extended Properties and the corresponding delete statement. Read more →

New Article series

I have started a new article series at Simple Talk. It's all about the transition from procedural programming to declarative programming. http://www.simple-talk.com/sql/  First article is found here http://www.simple-talk.com/sql/database-administration/the-road-to-professional-database-development-set-based-thinking/  And it is already viewed 5500 times. 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 →

MVP renewed

I got an email last friday telling me I was to keep my MVP status! What do one say about that? Except "Thank you". To all that reads my articles and posts. Read more →

Feedback from SQLBits 8

This years SQLBits occurred in Brighton. Although I didn’t have the opportunity to attend the full conference, I did a presentation at Saturday. Getting to Brighton was easy. Drove to Copenhagen airport at 0415, flew 0605 and arrived at Gatwick 0735. Read more →

A story from SQLvdb and Idera

A year or so back, I struggled with some consistency problems so I figured out I needed a way to "mount" backup files as a virtual database. At the time (SQL Server 2005 and SQL Server 2008) my choice fell on Idera's SQLvdb because it felt easy enough to use. 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 →