T-SQL ISO 8601 Week, Year and Start Date

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

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!

DYMO Label Framework Typescript Definition

About a week ago I’ve created a Typescript definition file (.d.ts) for the DYMO Label Framework. Being the good netizen I am, I created a pull request on DefinitelyTyped (the de facto Typescript definition repository) and it was accepted! Well, the second time round it was…

So, using the amazing tsd tool (kind of like the bower for typescript definition files), you can now do…

$ tsd install dymo-label-framework

…and have the Typescript definition file automatically added to your project. This will give you Intellisense in e.g. Visual Studio Code or VS2015 and compile-time type checking during Typescript to javascript compilation.

I’ve done my best to add all documentation to the definition file, so you can enjoy proper Intellisense with full documentation during programming. Enjoy!

Assembly.Load and FileNotFoundException

I’m experimenting with AppDomains to be able to load multiple versions of the same assembly in one application. I’ll write a bigger post about my findings later, but there’s one thing I encountered very early in the experiments: using Assembly.Load on a newly created AppDomain immediately leads to a FileNotFoundException. It took me a while to figure out why that happened and I’d like to share my experience with you.
Continue reading Assembly.Load and FileNotFoundException

Profiling on SQL Server Express

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

Fighting “General SQL Error” in Dynamics CRM custom workflows

Working for my client Valx, I had some serious problems in custom workflows for Microsoft Dynamics CRM 4. Every now and then, a workflow would throw a SoapException. I always put the original exception in the InvalidPluginExecutionException (second parameter “inner exception”). Now the actual exception information you want to see is in SoapException.Detail, which is an XmlNode. You won’t see that info if you just put the SoapException in the InvalidPluginExecutionException’s inner exception. Therefore, I use the following try/catch construction in each custom workflow’s Execute method to see some relevant information in the WorkflowLog view of the Dynamics CRM database.

Continue reading Fighting “General SQL Error” in Dynamics CRM custom workflows

PDF watermark/background Rendering Extension for SSRS – Part 2

Updated 2011-02-22: the underlying Stream is closed when PdfWriter.Close() is called, so the code for the PdfHandler.AddBackgroundPdf() method is updated (line 79 and further).

Well, I didn’t think this follow-up to Part 1 would come this quick, but here it is! Again I would like to emphasize that Jan Hoefnagels is the one who should take credit for this solution, but he isn’t the blog-writing type of guy. He’s not even a “Getting Things Done” guy. He’s a “Making Things Work” guy. And he’s pretty good at that.

Anyway, Part 1 showed you a general solution for an implementation of a Rendering Extension for SQL Server Reporting Services that was able to utilize the built-in (Microsoft provided) PDF Renderer, while enabling you to get the rendered PDF and do “something” with it, before sending it back to the SSRS server, which would subsequently send it to the end-user as a downloadable PDF file. That was a long sentence by English standards, but by Dutch standards, that’s kind of like a normal length. In German however, you would just be getting started. This whole article could be one sentence in German and still leave room for more.

Continue reading PDF watermark/background Rendering Extension for SSRS – Part 2

PDF watermark/background Rendering Extension for SSRS – Part 1

While working on a Dynamics CRM implementation for Valx —whose website was made by Jan, Zlatan and me with Umbraco— with my dear friend and “colleague” Jan Hoefnagels, we stumbled upon a technological hurdle.

We promised our client quotes, orders and invoices —as generated by SQL Server Reporting Services, which is neatly integrated into Dynamics CRM— with and without their own letter background, so they could use the same reports for printing on pre-printed paper (no background), as well as send the PDF through email to their clients (with background). Continue reading PDF watermark/background Rendering Extension for SSRS – Part 1