Problem with dates on MsSQL

Questions and answers regarding general SQL and backend databases

Problem with dates on MsSQL

Postby udiluca » Wed Mar 15, 2017 7:01 pm

Hello,

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.

Thank you, I'm lost as you may imagine...

Here are 2 screenshots from the 2 environments
Attachments
Capture d’écran 2017-03-15 à 17.43.24.png
Capture d’écran 2017-03-15 à 17.43.24.png (68.08 KiB) Viewed 6279 times
Capture d’écran 2017-03-15 à 17.43.07.png
Capture d’écran 2017-03-15 à 17.43.07.png (76.66 KiB) Viewed 6279 times
udiluca
 
Posts: 24
Joined: Wed Apr 10, 2013 7:25 pm

Re: Problem with dates on MsSQL

Postby patrick » Thu Mar 16, 2017 10:41 am

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?
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Problem with dates on MsSQL

Postby udiluca » Thu Mar 16, 2017 11:49 am

Hi Patrick,

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

Thanks
udiluca
 
Posts: 24
Joined: Wed Apr 10, 2013 7:25 pm

Re: Problem with dates on MsSQL

Postby patrick » Thu Mar 16, 2017 11:53 am

I have had exactly that problem, used the very correct driver and the problem vanished. I use datetime2 all over the place...
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Problem with dates on MsSQL

Postby kwpsd » Thu Mar 16, 2017 8:34 pm

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.

And, as Patrick also stated, MSSQL uses two different date-time types, 'datetime' and 'datetime2'. Refer to this post to learn the differences: http://forum.servoy.com/viewtopic.php?f=4&t=17597

I hope this helps!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Problem with dates on MsSQL

Postby udiluca » Fri Mar 17, 2017 10:00 am

Hi,

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.

Thanks

Ugo
udiluca
 
Posts: 24
Joined: Wed Apr 10, 2013 7:25 pm

Re: Problem with dates on MsSQL

Postby patrick » Fri Mar 17, 2017 10:19 am

Please let us know if that fixes the problem. The very latest driver I see is version 6 from January 2017 (https://www.microsoft.com/en-gb/downloa ... x?id=11774).
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Problem with dates on MsSQL

Postby kwpsd » Sat Mar 18, 2017 10:18 pm

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.
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 6 guests