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

Editing datetime field in grid for MSSQL servers

User, date Message
Written by amour
10 months 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
10 months ago
4801 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
10 months ago
564 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
10 months 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
10 months ago
4801 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
10 months ago
564 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.