Error 3621 when editing a resultset

[expired user #8173]'s profile image [expired user #8173] posted 10 years ago in General Permalink
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.
kalvaro's profile image kalvaro posted 10 years ago Permalink
I assume you are not using a MySQL database. Is it SQL Server or Postgre?
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
SqlServer.
Don't know the versiin right now.
jfalch's profile image jfalch posted 10 years ago Permalink
use SELECT @@version
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
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.
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
Hi there,

Any idea somebody?
ansgar's profile image ansgar posted 10 years ago Permalink
Please copy+paste the UPDATE query here, and also the error message which appears after that.
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
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.
ansgar's profile image ansgar posted 10 years ago Permalink
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?
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
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.
ansgar's profile image ansgar posted 10 years ago Permalink
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.
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
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.
ansgar's profile image ansgar posted 10 years ago Permalink
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.
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
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.
ansgar's profile image ansgar posted 10 years ago Permalink
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.
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
You are right, sorry, I didn't notice the separator "/".

I tested your update statement and it works perfectly.
Code modification/commit from ansgar.becker, 10 years ago, revision 4796
MSSQL: Return date/time string value as expected by server. See http://www.heidisql.com/forum.php?t=15925
ansgar's profile image ansgar posted 10 years ago Permalink
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.
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
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
jfalch's profile image jfalch posted 10 years ago Permalink
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().
[expired user #8173]'s profile image [expired user #8173] posted 10 years ago Permalink
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...

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