A big thing to watch out for when you’re building an application in K2 blackpearl with an RDBMS back end is an understanding of how to treat datetime values.

K2 uses the value scnull to indicate a NULL value. This becomes especially important in SmartObject calls down to, say, stored procedures in your database.

The problem is, you can’t pass a value of scnull into, say, a workflow datafield that’s expecting a datetime value. For that, you have to use an actual datetime value like 1/1/1900.

All of this manipulation can make things pretty hairy when you’re trying to feed a SmartObject and start a workflow at the same time. If your datetime value is on a SmartForm, and you anticipate having to deal with blank or NULL values, you might consider adding a hidden data label or two and expressions to go along with them. That way, you can convert the calendar control value into something useful for the database in one data label, and something useful for data fields in another.

I’m working on a little home project right now that does some examination of the current day of the week against a database table containing events by year and week number.

Curious to know if my event for this week would run as scheduled, I did some checking. Microsoft SQL Server thinks that we’re currently week 53 of 2015, instead of week 1 of 2016.

But Outlook is telling me we’re in week 1 of 2016, because January 1st is on Friday! How can this be?

My workaround was to duplicate the event for week 1 of 2016 in a new record for week 53 of 2015, but the reason seems worth exploring.

Based on various articles I’ve read, including this post I found on Stack Overflow, I’m led to believe that SQL Server is smart enough to know the difference between the last days of the current year and the first days of the new in the context of the week that bridges the two years.

Any days in the current year that extend beyond week 52 (starting on Sunday) are called week 53, and any days in the coming year in the same calendar week (up through Saturday) are considered week 1. The next Sunday starts week 2.

Example: This year, December 27th fell on Sunday, and January 1st is on Friday. So, from a SQL Server perspective, Sunday, December 27th through Thursday, December 31st are week 53; Friday, January 1st and Saturday, January 2nd are week 1, and Sunday, January 3rd through 9th are a full week 2.

So my week 53 workaround should work as long as the event day is before January 1st.

SQL Server also has the concept of the ISO WEEK datepart. Ask your doctor if it’s right for you.


I was recently asked if one can determine whether a specific person has a specific process instance in his/her worklist. The answer is yes — an administrator can do this from the K2 Workspace if some attributes about the process instance are known.

Continue reading