Can't update DateTime column in MS SQL table
UPDATE MyTable SET "StartDate"='30/01/2015 00:00:00' WHERE "Id"=59;
which leads to an error:
/* SQL Error (3621): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
The statement has been terminated. */
Rather use ISO 8601 date format, which is always unambiguous and independent of the locale, i.e.:
UPDATE MyTable SET "StartDate"='2015-01-3000:00:00' WHERE "Id"=59;
I'm asking because another user provided the current format as some universal format, which should work on all MSSQL servers. We know now that this is not true, so I must be sure to have that tested on other servers as well before I put that into HeidiSQL.
UPDATE "master"."dbo"."spt_monitor" SET "connections"=18 WHERE "lastrun"='2012-02-10 21:02:09';
But it also does not update any row in my table. So something is still wrong here.
See this post for even more confusion on date/time values in MSSQL. The user said that this ISO format throws a similar error (in Spanish).
CREATE TABLE "spt_monitor" ( "lastrun" DATETIME NOT NULL DEFAULT NULL, "cpu_busy" INT NOT NULL DEFAULT NULL, "io_busy" INT NOT NULL DEFAULT NULL, "idle" INT NOT NULL DEFAULT NULL, "pack_received" INT NOT NULL DEFAULT NULL, "pack_sent" INT NOT NULL DEFAULT NULL, "connections" INT NOT NULL DEFAULT NULL, "pack_errors" INT NOT NULL DEFAULT NULL, "total_read" INT NOT NULL DEFAULT NULL, "total_write" INT NOT NULL DEFAULT NULL, "total_errors" INT NOT NULL DEFAULT NULL, "Spalte 12" INT NULL DEFAULT NULL, "h" HIERARCHYID NULL DEFAULT NULL, PRIMARY KEY ("lastrun") ) ;
However, when I run this query, no data is changed:
UPDATE "master"."dbo"."spt_monitor" SET "connections"=18 WHERE "lastrun"='2012-02-10 21:02:09.093';
And it returns -10000 on this query:
select datediff(ms, lastrun, '2012-02-10 21:02:09.092') * 10000 TimeDiff from master.dbo.spt_monitor;
However, why did the above UPDATE not change the value for "connections" from 19 to 18, if the datetime is correct? I'm confused.
CREATE TABLE "test" ( "id" INT NOT NULL DEFAULT NULL, "value" DATETIME2 NULL DEFAULT NULL, "v2" DATE NULL DEFAULT NULL, "col3" DECIMAL NULL DEFAULT NULL, "col4" MONEY NULL DEFAULT NULL, "col5" TEXT NULL DEFAULT NULL, "Column 4" DATETIME NULL DEFAULT NULL, PRIMARY KEY ("id") );
Same problem - 0 rows updated when doing this:
UPDATE dbo.test SET col5='hello' WHERE "Column 4"='2015-11-02 20:58:00.123'
While Management Studio shows the microseconds:
create table TestDates ( Id int not null identity(1,1), DateOnly DATETIME not null, DateAndTime DATETIME not null, DateAndTimeWithMilliSeconds DATETIME not null, Tag varchar(5) null primary key (Id) ) insert into TestDates (DateOnly, DateAndTime, DateAndTimeWithMilliSeconds) values ('2011-01-12', '2011-01-12 01:52:42', '2011-01-12 01:52:42.449') insert into TestDates (DateOnly, DateAndTime, DateAndTimeWithMilliSeconds) values ('2012-02-12', '2012-02-12 02:58:19', '2012-02-12 02:58:19.654') insert into TestDates (DateOnly, DateAndTime, DateAndTimeWithMilliSeconds) values ('2013-03-12', '2013-03-12 03:08:17', '2013-03-12 03:08:17.449') -- select * from TestDates update TestDates set Tag = 'T1' where DateOnly = '20110112' update TestDates set Tag = 'T2' where DateAndTime = '20120212 02:58:19' update TestDates set Tag = 'T3' where DateAndTimeWithMilliSeconds = '20130312 03:08:17.449' select * from TestDates
I also have the same problem as Igitur with SQL Servers with Turkish locales. Also, his original suggestion to use ISO format is correct. It should work for everybody regardess of locale or collation settings.
The reason your updates are not working, Ansgar, is not due to errors with Heidi or ISO format. The problem is caused by time precision in SQL Server. You'll find that SQL Server cannot process DateTime values with millisecond accuracy. Here's an interesting experiment:
SELECT SERVERPROPERTY('productversion'), CONVERT(DATETIME, '2015-12-31 23:59:59.998'), CONVERT(DATETIME, '2015-12-31 23:59:59.999')
I executed this on two servers close at hand:
"9,00,1406,00" "2015-12-31 23:59:59" "2016-01-01"
"10,50,2500,0" "2015-12-31 23:59:59" "2016-01-01"
You'll find that DATETIME2 data type has better precision, but it's added after SQL 2008, so it will fail on older versions:
SELECT SERVERPROPERTY('productversion'), CONVERT(DATETIME2, '2015-12-31 23:59:59.998'), CONVERT(DATETIME2, '2015-12-31 23:59:59.999')
SQL Error (243): Type DATETIME2 is not a defined system type.
"10,50,2500,0" "2015-12-31 23:59:59.9980000" "2015-12-31 23:59:59.9990000"
Long story short, you should never rely on exact date comparisons in your WHERE clauses. If I'm querying with date criteria, I always specify date ranges, as opposed to exact moments in time. The fact that your updates are not working does not mean that the ISO format is wrong, but rather some invisible rounding errors are taking place during the comparison. Granted, I was not able to reproduce the tag update failure with Igitur's code either, but I'm pretty sure it has to do with how the SQL servers are handling the rounding errors - possibly a configuration or locale issue.
Also it's great that you posted that last date example because it also accentuates other dangers of using region-specific date formats. Here's another test:
INSERT INTO TestDate (MyDate) VALUES ('10/02/2012 21:02:09'); --SQL generated by Heidi INSERT INTO TestDate (MyDate) VALUES ('2012-02-10 21:02:09'); --SQL with ISO 8601 (T omitted) INSERT INTO TestDate (MyDate) VALUES ('2012-02-10T21:02:09'); --SQL with ISO 8601 select * from TestDate
We use dd.MM.yyyy in Turkish. Both February 10 and October 2 are valid dates. So:
Whoops. Suddenly that important shipment is 8 months late.
Oh, thanks for the great tool, BTW.
Use ISO 8601 date/time format on MSSQL. See http://www.heidisql.com/forum.php?t=17728
I'm now spending some time to find the cause of the missing milliseconds part of the cell values. The following query reveals the milliseconds by using a CONVERT() function call:
SELECT lastrun, CONVERT(VARCHAR, lastrun, 21) FROM master.dbo.spt_monitor
Why is the milliseconds part omitted when just selecting "lastrun", a DATETIME column? Is it caused by the ADO driver HeidiSQL is using to connect to MSSQL?
* Fix microseconds in MSSQL date/time data types, hidden in data and query grids.
* Add support for microsecond precision of MSSQL date/time types in table editor, show these in "Length/Set" column
* See http://www.heidisql.com/forum.php?t=17728
* Microsecond precision is now displayed in table structure editor, in column "Length/Set"
* ... and these are editable for DATETIME2 types, not so for DATETIME types, for which I got an SQL error when trying to pass a precision to the ALTER TABLE query.
* Microseconds should be displayed in data and query grids now, for both DATETIME and DATETIME2 columns.
One thing which I can't get to work due to a bug in the ADO implementation: DATETIME2 columns are detected as ordinary text fields in query grids, and are displayed with green color as such. The "Data" tab does not have this bug, as it gets the field types from INFORMATION_SCHEMA.COLUMNS.
Please login to leave a reply, or register at first.