Error 3621 when editing a resultset
I'm on version 220.127.116.1194.
I run a query and try to edit the resultset, when postig the changes it throws me an 3621 error.
The reason is that dates in the resultset shows as yyyy-mm-dd, so when you edit a resultset it creates an update statament where WHERE clause contain dates with that same format yyyy-mm-dd, but in order to run an update statement correctly it needs dates in yyyy-dd-mm or dd-mm-yyyy formats.
Any solution to this?
Thanks a lot.
Here is what "SELECT @@version" returns:
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86)
Sep 22 2011 00:28:06
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) (VM)
UPDATE "VALIDACION_DB"."dbo"."M4SSP_AFI_MOVIM" SET "SSP_ID_ACCION"='11' WHERE "STD_ID_HR"='0103711' AND "STD_OR_HR_PERIOD"=1 AND "DATE"='2011-12-31' AND "SSP_ID_ACCION"='10' AND "ID_ORGANIZATION"='01' AND "SSP_OR_CESION"=0;
The error message:
/* SQL Error (3621): La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo
Se terminó la instrucción. */
Translated to english it would be something like:
The data type conversion from varchar to datetime leads to an out of range value.
I never said that HeidiSQL should display dates in YYYY-MM-DD either.
I'm only exposing the facts that I have been able to prove:
1) HeidiSQL shows dates in query results in YYYY-MM-DD format.
2) The update statement generated when editing a query result uses dates also in YYYY-MM-DD format.
3) If I want to execute an update in HeidiSQL , I have to write dates in YYYY-DD-MM or DD-MM-YYYY format, both of them work.
Maybe HeidiSQL should show dates in one of the formats whick work, or maybe it should generate the update statement with another date format, I really don't know, I'm just speculating.
UPDATE "VALIDACION_DB"."dbo"."M4SSP_AFI_MOVIM" SET "SSP_ID_ACCION"='11' WHERE "STD_ID_HR"='0103711' AND "STD_OR_HR_PERIOD"=1 AND "DATE"='31/12/2011' AND "SSP_ID_ACCION"='10' AND "ID_ORGANIZATION"='01' AND "SSP_OR_CESION"=0;
Please confirm that this works without errors for you.
If I understand right, what you propouse is to change a parameter in the database, is that right?
When I post this issue I was looking forward an answer which puts me in the direction to change something in HeidiSql config. I was hoping somewhere in HeidiSql where you choose the date format or something like that, as we are not allowed to change any database parameter.
If this is not possible, changing HeidiSql config, then I would expect HeidiSql to read from the database the parameter which especifies date formats, show results in this very format and generate update wh3n editing in that format as well.
I am looking for a solution which I can use to modify HeidiSQL in a new build, which makes date/time updates functional.
You can help with that by copying the above query and paste it into your query editor. Then, run it with F9 and report here whether that worked or returned some error message.
Regarding the update statement, without testing that very statement I can assure you it would work fine, because as you can see in one of my previous posts that's one of the two date formats accepted by our database:
Thanks for everything.
MSSQL: Return date/time string value as expected by server. See http://www.heidisql.com/forum.php?t=15925
I meant that HeidiSQL is showing dates in YYYY-MM-DD by default, and I would like to change this "by default parameter", if it exists.
Using convert function in every select statement I write is not an option.
If there is no parameter where you change the default date format, at least it should be reading if from the database...
Please login to leave a reply, or register at first.