distal-attribute
distal-attribute
distal-attribute
distal-attribute

Editing datetime field in grid for MSSQL servers

amour posted 1 year 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 1 year 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 1 year 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:

'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))

amour posted 1 year ago
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.
ansgar posted 1 year ago
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?

kalvaro posted 1 year 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.