SQL Error (3621)

justinshea posted 12 months 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 12 months ago
Here is URL to forum post
justinshea posted 12 months ago
so no one can offer any help?
ansgar posted 12 months 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 12 months 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 11 months ago
thanks gents - both answers were quite helpful

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