Microsoft SQL Server 2008+ TIMESTAMP problem

Question

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

• George.Palacios: Can you not insert the records in the format that works?
• lumo: as stated in my question, nope i cannot change the format... and it was working on the very old mssql server from 2000

Answer 1

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.

enter image description here

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.