Symptoms

When using a SQL SmartObject and exposing a Date or DateTime type column on a View via Calendar Control, then selecting a certain date and saving it, K2 server performs time zone conversion which may lead to displaying the wrong date when retrieving the data in Views afterwards in certain scenarios. This happens even if underlying data type is Date and there is no "Date" data type on SmO/control level in versions prior to 4.6.9
 

Diagnoses

So apparently this is a known issue, and in the past (before 4.6.9), the workaround was to apply a time zone conversion of 12 hours, depending where you are in the world, via a hidden label / expression on the Form/View before saving the data to SQL. However, after 4.6.9, two new data types were introduced, namely "Date" and "Time". Both don't convert data when saving to SQL, so my recommendation here would be to modify the SQL Table and change the column data type to "Date", or split the two columns and make one "Date" and the other "Time".

Also, you can edit the Calendar control on the Form/View and enable the saving of time as well, so it will save the Date and the Time (together as DateTime). You'll still need to apply the time zone conversion of 12 hours (or depending where you are in the world).

The DateTime value is stored in SQL in the server time relative to the user time. This means the time that the server clock would show at the point in time that the clock on the client browser shows the time selected in the calendar control. From 4.6.9 onwards, you can use either Date or DateTime in SmartForms. When only entering a Date but using a DateTime type the time will be selected as midnight (00:00) in the client browser time zone.

The conversions are done by K2. It is done as follows:
Local date time in the control >>converted>> to UTC (via JavaScript in the browser) >>transferred>> IIS (SmartForms) >>converted>> to server time >>transferred>> K2 host server >>transferred>> SQL Server

The Date type does not contain a time or time zone and no conversions are done anywhere.

 

Resolution

Instead of using "DateTime" data type, please change your SQL DB to use "Date" and "Time" data types, then refresh your Service Instances and SmartObjects appropriately.

This is the preferred method of doing this since 4.6.9

Alternatively, you can apply a time zone conversion of 12 hours (depending on your location in the world) before saving the data to SQL.