Storing DateTime in database

Hello,

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 ?

database_conversion

asked 23.03.2015 at 17:45

dbalasse
(suspended)
accept rate: 25%

One Answer:

active answers oldest newest most voted

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

link

answered 25.03.2015 at 16:34

Peter Stöckli ♦
(suspended)
accept rate: 69%

edited 25.03.2015 at 16:36

Hi,

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

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

(26.03.2015 at 10:24) Peter Stöckli ♦

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 ♦

Your answer

toggle preview

community wiki:

Follow this question

By Email:

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

By RSS:

Answers

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

learn more about Markdown

Tags:

database_conversion ×3

Asked: 23.03.2015 at 17:45

Seen: 1,748 times

Last updated: 30.03.2015 at 16:43

Storing DateTime in database

Follow this question

Related questions