0

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

2
  • 2
    Can you not insert the records in the format that works? May 24, 2017 at 10:15
  • as stated in my question, nope i cannot change the format... and it was working on the very old mssql server from 2000
    – lumo
    Jun 6, 2017 at 14:40

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.