Error 3621 when editing a resultset

Hello,

I'm on version 8.3.0.4694.

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.

I assume you are not using a MySQL database. Is it SQL Server or Postgre?

SqlServer.
Don't know the versiin right now.

use SELECT @@version

Hello again,

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)
---------------------------------------------------------

Thanks.

Hi there,

Any idea somebody?

Please copy+paste the UPDATE query here, and also the error message which appears after that.

The update query:

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.

And you say your server expects dates in the format YYYY-DD-MM ? But why should HeidiSQL then display dates in YYYY-MM-DD format?

I don't know if is the server itself which expects dates in that format, or maybe is a format problem in HeidiSql.

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.

I think SQL server has something I would call a default date/time format. I found some documentation on dates and I guess this one should work for you, and I would like you to test that:

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.

Sorry for the delay on the answer.

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.

You misunderstood me.

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.

Wow, sorry for that man!

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:

DD-MM-YYYY
YYYY-DD-MM

Thanks for everything.

Does not look equal to me:
"31/12/2011"
"DD-MM-YYYY"

Please test, so we know for sure it works before I put effort into it.

You are right, sorry, I didn't notice the separator "/".

I tested your update statement and it works perfectly.

r4796 should work for all date/time values in MSSQL. Please update your HeidiSQL with the latest build and report back if it works for you.

It works like a charm!

Thanks a lot for the effort :)

If you allow me, just one more question regarding date formats.

Is it possible to change the way dates are shown when you throw a select statement? For us it's showing them in YYYY-MM-DD format.

Thx

for MSSQL see here, replacing getdate() with your date field; the main thing appears to be the convert functions´ style argument.

for mysql, this would be date_format().

Thank you but I already know the convert function in MSSQL....

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...