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

Editing datetime field in grid for MSSQL servers

User, date Message
Written by amour
1 year ago
Category: Feature discussion
2 posts since Thu, 04 Jul 13
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?
Written by ansgar
1 year ago
4973 posts since Fri, 07 Apr 06
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?
Written by kalvaro
1 year ago
592 posts since Thu, 29 Nov 07
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))

Written by amour
1 year ago
2 posts since Thu, 04 Jul 13
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.
Written by ansgar
1 year ago
4973 posts since Fri, 07 Apr 06
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?
Written by kalvaro
1 year ago
592 posts since Thu, 29 Nov 07
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.