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

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.

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.