Editing datetime field in grid for MSSQL servers

The software throws out error when I tried to edit datetime fields for MSSQL servers.

It seems like MSSQL accepts the dd/mm/yyyy format but Heidi emits yyyy-mm-dd.

Has this been brought out before?

No, never heard before. Is that dd/mm/yyyy format always the same in MSSQL or is there some local setting which HeidiSQL should read out before starting the editor?

I've been unable to find the official documentation about this but in the INSERT Examples (Transact-SQL) page they include an example that uses the following format:

'20080414'

Some forum entries suggest that SQL Server will parse strings automatically but the order of items is configurable. In the same forum thread we can find what seems to be a bulletproof approach (though I don't know whether it's overkill):

INSERT INTO MyTable (MyDate)
Values (Convert(DateTime,'19820626',112))

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/11/13/how-to-change-date-format-after-installing-sql-server.aspx

I run "dbcc useroptions" on my mssql server and it shows my date format to be dmy.

Using some

CONVERT(DATETIME/DATE/TIME, '20130101', 112))

really looks like overkill to me. There must be some basic default way to insert date/times. I also guess we can always use the "20130704161400" syntax, or?

Something called "ODBC date literal syntax" seems to work in HeidiSQL:

select {d'2000-02-29'}, dateadd(day, 1, {d'2000-02-29'})

Taken from http://stackoverflow.com/a/17547423/13508 (the from entry offers a good summary anyway).