we use database (sqlserver) to store values. One of those field is an ivy DateTime. When we try to store it in the database we have this error :

com.microsoft.sqlserver.jdbc.SQLServerException: The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)

The ivy field is a ch.ivyteam.ivy.scripting.objects.DateTime and it must be stored in a smalldatetime filed from sqlserver (10.50.4000.0).

What are we doing wrong ?

asked 23.03.2015 at 17:45

dbalasse's gravatar image

accept rate: 25%

Hi there

Possibilities I could think of:

  • Date is out of range for MS SQL. auto initialized DateTimes are '0001-01-01 00:00:00', but MS SQL accepts only a date between January 1, 1753 and December 31, 9999
  • The language of the MS SQL user isn't english -> DateTime Format is different SET LANGUAGE

answered 25.03.2015 at 16:34

Peter%20St%C3%B6ckli's gravatar image

Peter Stöckli ♦
accept rate: 69%

edited 25.03.2015 at 16:36


i've solved this by changing the DateTime to a java.sql.Date. Then I format this date in a string with the right format ("yyyy-MM-dd hh:mm:ss").

It worked well on my dev environment but on the client's TST env, the format was different, days and month were inverted ("yyyy-dd-MM hh:mm:ss")

In the futur, what would be an "ivy" standard way to store Date ?

Thanks for your answer.

(25.03.2015 at 16:45) dbalasse dbalasse's gravatar image

Hi what is the language of the MSSQL DB User in use?

(26.03.2015 at 10:24) Peter Stöckli ♦ Peter%20St%C3%B6ckli's gravatar image

Have you tried using the jTDS driver for MSSQL? (You can switch the driver in use in the Databases configuration UI)

(30.03.2015 at 16:43) Peter Stöckli ♦ Peter%20St%C3%B6ckli's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 23.03.2015 at 17:45

Seen: 1,262 times

Last updated: 30.03.2015 at 16:43