I'm currently facing a Problem, where the SQL Server refuses my Timestamps. I am accessing the Server via EclipseLink (Java) but this shall not be the problem, as i can test the queries on the Server and receive the same results.
i created a small sample to reproduce this problem :
CREATE TABLE testDateTimes ( id int not null, ts datetime not null default CURRENT_TIMESTAMP )
insert values (one value generated by the server)
insert into testDateTimes (id) VALUES (1)
querying the values from the datetime
select * from testDateTimes;
1 2017-05-24 10:45:17.267
now trying to manually insert a new datetime:
insert into testDateTimes (id, ts) VALUES (2, '2017-05-24 12:34:56.789')
which fails with error message: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
i also noticed that this would be valid too:
insert into testDateTimes (id, ts) VALUES (2, '20170524 12:34:56.789')
but i can't change the way/format how EclipseLink sends the Data.
if i transform the datetime to another format:
insert into testDateTimes (id, ts) VALUES (2, '24.05.2017 12:34:56.789')
it is working...
NOTE: i am sure there are no errors in the DateTime itself (this is verified by Java) How can i specify to take the correct format? standard and unique SQL/ISO date format instead of the one with the Dots.? Note that i cannot do this in every query. It should be a default setting from the Server.
(i might be wrong, but this problem seems to pop up after moving from MSSQL2000 to MSSQL2008)
when i execute
set language 'croatian'
sys.syslanguages says croatian incldues dateformat ymd, the queries work as expected.
also SET DATEFORMAT ymd;
does solve the problem, but it is not a permanent solution.
update sys.syslanguages set dateformat = 'ymd' where name = @@LANGUAGE
would solve the problem, BUT this is prevented by the server!
ad hoc updates to system catalogs are not allowed