MSSQL DateTime update error in 9.2

We are facing a regression with date time formatting on MSSQL after upgrading from 9.1 to 9.2. When editing or adding a row with datetime value in it via UI we are getting the following:

INSERT INTO "DBNAME"."dbo"."table_name" ("id1", "id2", "id3", "id4", "date_add") VALUES (1, 1542, 1, 1539, '2015-05-15 12:21:50.000');

which results in

/* SQL Error (3621): Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона
Выполнение данной инструкции было прервано. */

(sorry for Russian error message, but that's what reported by the server. English should be "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." )

Changing format in request manually does the tirck:

INSERT INTO "DBNAME"."dbo"."table_name" ("id1", "id2", "id3", "id4", "date_add") VALUES (1, 1542, 1, 1539, '20150515 12:21:50.000');

This is really unfortunate, as it was OK in previous version, and now it renders Heidy to be unusable.

There was a long discussion at http://www.heidisql.com/forum.php?t=17728 and after that it was changed. I've found so many conflicting information about which is the only truly unambiguous date format, and as you can see in that thread, we tried different formats in different locales.

According to http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx we should use one of these 2:

YYYYMMDD
YYYY-MM-DDThh:nn[:ss[:mmm]]

I'm happy to go through that whole process again if it means we can fix this for all users in all locales.

Thank you for the answer and sorry for taking that long to respond.

As for me, I've found a workaround for the problem - had db admin to change "Default Language" to English (from Russian) in my user settings on the MSSQL server. Which is OK for me but really not a perfect solution.

I've read both your links, and checked the formats. Thus I confirm that with English set as a "Default Language" both YYYY-MM-DDThh:mm:ss.fff and YYYY-MM-DD hh:mm:ss.fff formats work. But when "Default Language" is Russian, only YYYY-MM-DDThh:mm:ss.fff works, and YYYY-MM-DD hh:mm:ss.fff does not work.

While I surely prefer to not have that 'T' inside the date for readability reasons, it seems to be universal indeed.

The other solution would be to have some kind of setting for the connection and/or showing the date in localized format to user, but converting it to universal format when passing to server. But this could cause more problems than solve.

Yeah, changing the locale settings isn't a proper solution.

ansgar, how about changing to YYYY-MM-DDThh:mm:ss.fff ?

Well I'm open for any universal format. I only tested so many different formats already, so I always have a gutt feeling I'm going to break something for older or newer SQL servers. However, I can test that at least here on my own MSSQL Express 2012 and report if that works.

r4960 now automatically injects the "T" between the date and time portion of a DATETIME or DATETIME2 column value. Works nicely here. Please also update your HeidiSQL to the latest build and report back if that works for you.

Yeah, works just fine for me, both in 'where' clauses and in updates! Thanks!

Works for me too.