SQL Error (3621)

justinshea posted 3 years ago in General
I am receiving an error prompt while attempting to update a specific field for records in a specific table. Please see the screenshot and attached log files to see the update operation and ensuing error. I have attempted to do this operation using the specific build mentioned in [url=null]this forum[/url], and, I have also attempted using the most recent build as well. I continue to get the same error message.


log file
justinshea posted 3 years ago
Here is URL to forum post
justinshea posted 3 years ago
so no one can offer any help?
ansgar posted 3 years ago
So, the erronous SQL is here:
UPDATE TOP(1) "ad_live"."dbo"."advertisment_adInterstitial" SET "script"='test' WHERE  ... AND "create_date"='15/07/2009 08:43:56' AND ...

First, the WHERE statement contains all table columns, which is done by HeidiSQL only when the table does not have a primary key. If you add a primary key (probably on the "id" column), your problem should get solved as the WHERE statement then leaves out the "create_date" column.

Secondly, you could tell why MSSQL complains about that date comparison. Probably that '15/07/2009 08:43:56' is not a valid date/time syntax in MSSQL 11?
jfalch posted 3 years ago
AFAIK, what is or is not a valid date/time syntax is determined by the server´s dateformat option. (this, in turn, is pre-set by the language option).
SET dateformat dmy
should change the dateformat to accept 15/07/09, but i´m not sure about the '/'...

this lists alternatives using replace(convert(...))
justinshea posted 3 years ago
thanks gents - both answers were quite helpful

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