datetime2 data type

Classic Servoy
1

Servoy: Version: 6.0.4 - build 1227
SQL: Microsoft SQL Server 2008 Express (MSSQL10_50.SQLEXPRESS)

Microsoft has introduced a new data type: datetime2

+-----------+-------------------------+-------------------------+
|           |        datetime         |        datetime2        |
+-----------+-------------------------+-------------------------+
|     range | 01/01/1753 - 12/31/9999 | 01/01/0001 - 12/31/9999 |
+-----------+-------------------------+-------------------------+
| precision |   3 1/3 milliseconds    |     100 nanoseconds     |
+-----------+-------------------------+-------------------------+

When Servoy adds a datetime column to a MSSQL table (e.g. solution installation), MSSQL will default to the new ‘datetime2’ type. The JTIDS driver that comes with Servoy does not support the ‘datetime2’ type, and your solution will not display date values. Elsewhere on the Forum, someone suggested using the JDBC driver from Microsoft, however, we could not get it to work (we could not get past the ‘CLASSPATH’ error message). We ended up using the MS SQL Server Management Studio to change the ‘datetime2’ columns to ‘datetime’ as follows:

  1. Click on the ‘New Query’ button.

  2. Type ‘sp_help tablename’ to view the table structure (click the ‘!’ button to execute the statement)

  3. ALTER TABLE tablename
    ALTER COLUMNNAME columnname DATETIME

Highlight the two ALTER statements, then click ‘!’ to execute the statements

Hopefully, this will help others that run into this problem.

2

Kim,

Unfortunately, the jtds driver does not support the ‘new’ datetime2 type from sqlserver 2008.

There are already a few feature requests on the sourceforge site (where jtds is hosted), but that does seem to be picked up.

You have changed the type in the database.
Is that the development database?
I expect that when you import the solution in a new (test/prod) db and the columns do not exist yet, Servoy will create datetime2 again.

I guess going with the ms driver is best.
What is ‘CLASSPATH’ error message you refer to?

Rob

3

This problem (also in this topic: viewtopic.php?f=4&t=17536) has been fixed in Servoy 6.0.x and 6.1.

Regards,
Andrei

4

Rob and Andrei,

We developed our ARM solution on Sybase SQL Anywhere.

Last week, we installed the solution at the University of California (USC) in Los Angeles, CA but were mandated to use their Microsoft SQL Server 2008 RDMS (MSSQL). We spent 1.5 days trying to connect to their MSSQL using the Microsoft JDBC driver but could not get it to work. We resorted to the JTIDS driver but had trouble with the dates. Using MSSQL Server Management Studio to look at the table structures, we discovered the ‘datetime2’ change but, at the time, did not know the source…was it Microsoft MSSQL or Servoy? We now know it is Servoy:

Servoy upgraded its hibernate libs in release 6, in which the mapping for sqlserver2008 for timestamps was changed from ‘datetime’ to datetime2’

This has just been fixed in Servoy 6.0.x and 6.1.

Although we understand why Servoy made the datetime-to-datetime2 change, we believe a warning should have been issued to the development community that the JTIDS driver will not work with the ‘datetime2’ data type when using MSSQL. This is precisely why I created this post…to warn other developers.

By the way, what exactly is the ‘fix’? I am presuming that chosing the JTIDS driver as the connection will force the use of the ‘datetime’ data type while chosing the MSSQL JDBC driver will force the use of the ‘datetime2’ data type. Is this correct?