Editing datetime field in grid for MSSQL servers

amour posted 4 years ago in Feature discussion
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?
ansgar posted 4 years ago
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?
kalvaro posted 4 years ago
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:


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))
amour posted 4 years ago

I run "dbcc useroptions" on my mssql server and it shows my date format to be dmy.
ansgar posted 4 years ago
Using some
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?
kalvaro posted 4 years ago
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).

Please login to leave a reply, or register at first.