Microsoft SQL Server 2008+ TIMESTAMP problem 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;


returns me:

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
You cannot change default dateformat for all users without changing your language Which you do not want to do in your case.

You can create a new user with a default language which supports the dateformat you need will do the trick in this case. Then your application will need to use that login/user for connecting when doing the insert.

For example my test server and my login is set to following.



SELECT NAME, 
       alias, 
       dateformat 
FROM   syslanguages 
WHERE  langid = (SELECT value 
                 FROM   master..sysconfigures 
                 WHERE  comment = 'default language') 



  name=us_english
  
  alias=English
  
  dateformat=mdy


With your example I get the same error if I try this.

INSERT INTO testDateTimes (id, ts) VALUES (2, '13.05.2017 12:34:56.789')



  Msg 242, Level 16, State 3, Line 15 The conversion of a varchar data
  type to a datetime data type resulted in an out-of-range value. The
  statement has been terminated.


Creating a new login with DEFAULT_LANGUAGE =British

USE [master]
GO
CREATE LOGIN Mary8 WITH PASSWORD = 'A2c3456$#',  
CHECK_EXPIRATION = ON, 
DEFAULT_LANGUAGE =British 
GO
USE [test]
GO
CREATE USER [Mary8] FOR LOGIN [Mary8]
GO
USE [test]
GO
ALTER ROLE [db_owner] ADD MEMBER [Mary8]
GO


Now if I login with Mary8 and run same insert statement it will succeed.

SELECT SUSER_NAME(), USER_NAME();  
GO
INSERT INTO testDateTimes (id, ts) VALUES (2, '13.05.2017 12:34:56.789')
GO



  (1 row(s) affected)


Reference I used.