We started a new solution a few months ago, and when moving from our test environment to the production environment, this date problem occurs.
Both environment uses MSSQL Server and SQL Server Management studio with version 10.50.1617 on our production environment and version 10.50.16000.1 on our testing environment. For information, our third server, used for pre-production behaves as expected, and everything is ok in developer using Servoy 7.4.
The problem, as strange as it may be, is that the dates from one server to the other differs from 2 days, when showed on a servoy form. We backup and restored the database from our production environment to the testing one, and the data differs from one to the other with the same datas. The date is thus correct in testing/pre-prod and servoy developer but differs in production with this problematic bug.
The solution deployed is the same, the driver and Servoy versions are the same, the servoy locale settings are the same, so what may happen according to you ?
Additonnal notes : This does happen with this new solution where dateTime gets converted to dateTime2 in MSSQL which is not the case with our previous solutions. We are using the microsoft driver, not the jtds.
From what I read you already tried to prevent all pitfalls (MS Driver, …). I have seen this before and all I can say is that in my case it was the JDBC driver. I downloaded the very correct driver for the SQL Server version and the problem vanished. You have looked at the servoy.use.client.timezone setting?
The setting is the same on all servers, that is servoy.use.client.timezone=true
I switched the column property in MSSQL from DateTime2 to DateTime and the result now is correct. I don’t know if my future imports will not switch back to DateTime2.
I downloaded the latest driver from microsoft, I will give some feedback on this
Patrick is absolutely correct when he says it’s the driver. If you are using the JTDS driver with MSSQL, that is your problem. Download and use the JDBC driver supplied by Microsoft. I believe the most recent version is 4.2 (sqljdbc42.jar). Googling for the driver is much faster than trying to navigate Microsoft’s web site. By the way, using the JTDS driver with MSSQL causes other problems than just the date problem.
As stated we are using the microsoft driver, not the jtds.
The only difference is the MsSQL version of the database servers ( 10.50.1617 on our production environment and 10.50.16000.1 on our testing environment ) and we’re using the same driver version on both application servers. Altering the date column so that its type is dateTime instead of dateTime2 solved the problem for the moment, but we will switch from sqljdc4.jar to sqljdbc41 during the week-end in order to control things.
As stated we are using the microsoft driver, not the jtds.
Thanks for point that out…I missed it on the first read. To all…DO NOT use the JTDS driver with MSSQL as it is error prone.
We experimented with the preliminary version of Microsoft’s JDBC driver 6.0 late last year, but did not trust it for production work. The release to which Patrick refers, is the official release. If you decide to use this driver, I would be interested in how it works for you.
