Category: SQL Server

The ISO 8601 week dates standard seems very simple, but is hard to use in (T-)SQL. Let’s start with a couple of example dates:

• 2012-01-01 (Sunday) is in 2011-W52, first date (Monday) is 2011-12-26
• 2013-01-01 (Tuesday) is in 2013-W01, first date (Monday) is 2012-12-31
• 2016-01-01 (Friday) is in 2015-W53, first date (Monday) is 2015-12-28

The easy part is getting the ISO week number from a datetime. Executing DATEPART(ISO_WEEK, '2012-01-01') yields 52, so that’s correct. But determining the ISO week-year is harder. YEAR('2012-01-01') yields 2012 which is correct as a date, but not for the ISO week-year (which is 2011, see example above).

The following determines the ISO week-year. First, Monday is set as the first day of the week. Than, the difference between Thursday (day number 4) and the given day of the week is added to the given date. The year of the calculated date (which is the Thursday in the week of the given date) is the ISO week-year.

-- Input
DECLARE @MyDate datetime = '2012-01-01';

-- Configuration
SET DATEFIRST 1; -- Monday is first day of the week

-- Execute
SELECT YEAR(DATEADD(DAY, (4 - DATEPART(WEEKDAY, @MyDate)), @MyDate))

The query above returns 2011, which is correct.

The following determines the first date of a given ISO week. When the first day of the given year is Monday, Tuesday, Wednesday or Thursday, it is in ISO week 1 of the given year. Otherwise, it is in the last ISO week of the previous year. Next, we determine the date of the Monday in the week in which the first day of the given year falls. Finally, we add the number of given ISO weeks to that date, corrected with whether the first day of the year is in week 1 or not.

-- Input
DECLARE @Year int = 2013;
DECLARE @Week int = 1;
 
-- Config
SET DATEFIRST 1; -- Monday is first day of the week
 
-- Calculate
DECLARE @FirstDayOfYear datetime = DATEFROMPARTS(@Year, 1, 1);
DECLARE @IsFirstDayInFirstWeek int = CASE WHEN (DATEPART(WEEKDAY, @FirstDayOfYear) <= 4) THEN 1 ELSE 0 END;
DECLARE @StartOfWeek datetime = DATEADD(WEEK, @Week - @IsFirstDayInFirstWeek, DATEADD(DAY, (1 - DATEPART(WEEKDAY, @FirstDayOfYear)), @FirstDayOfYear));

-- Execute
SELECT @StartOfWeek

The query above returns 2012-12-31, which is correct.

Take note that DATEFROMPARTS works from SQL Server version 2012, but that it can easily be replaced using string concatenation to implicitly create a datetime. Furthermore, SET DATEFIRST 1; has session-scope, so any date calculations during the SQL session may be affected!

Try the queries with the examples to see that they work. Hope this helps!

I’m working on a new web app using ASP.NET MVC 3 and Entity Framework 4.1. One thing lacking in EF 4.1 is the ability to monitor the actual queries sent to the SQL database, like you could do with Linq2SQL’s DataContext.Log.

The machine I’m working on has SQL Server Express. The Express version doesn’t come with the SQL Profiler. So without Profiler and without a log function on Entity Framework’s DbContext, what’s a developer to do?

Well, as it turns out, profiling is part of the SQL Server (any version, including Express), the SQL Profiler application is just one way to access the profiling functionality. Some people even argue it’s better to do the profiling on the server itself, as opposed to through SQL Profiler.

Starting the trace

How do you start? The easiest way to start is (you’re not going to like this) by using SQL Profiler. That’s what I did (on another machine). It’s explained on the website linked above, here are the steps: Continue reading Profiling on SQL Server Express