SQL Error (3621)

[expired user #8304]'s profile image [expired user #8304] posted 10 years ago in General Permalink
Hi,
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.

screenshot:


log file
[expired user #8304]'s profile image [expired user #8304] posted 10 years ago Permalink
Here is URL to forum post
[expired user #8304]'s profile image [expired user #8304] posted 10 years ago Permalink
so no one can offer any help?
ansgar's profile image ansgar posted 10 years ago Permalink
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's profile image jfalch posted 10 years ago Permalink
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(...))
[expired user #8304]'s profile image [expired user #8304] posted 10 years ago Permalink
thanks gents - both answers were quite helpful

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