Can't update DateTime column in MS SQL table

igitur's profile image igitur posted 9 years ago in General Permalink
When editing and posting a change to a DateTime column in MS SQL, the following statement is executed:

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;
ansgar's profile image ansgar posted 9 years ago Permalink
Is that ISO 8601 format compatible to other versions of MSSQL?

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.
igitur's profile image igitur posted 9 years ago Permalink
ISO 8601 should be the universal format. That's why it's an ISO recommendation. I've tested it on SQL 2000, SQL 2008 R2 and SQL 2014.
ansgar's profile image ansgar posted 9 years ago Permalink
Super. So I'll give it a try.
ansgar's profile image ansgar posted 9 years ago Permalink
No luck on my MS SQL Express 11.0 server:
UPDATE "master"."dbo"."spt_monitor" SET "connections"=18 WHERE  "lastrun"='2012-02-1021:02:09';
/* SQL Error (241): Conversion failed when converting date and/or time from character string. */
igitur's profile image igitur posted 9 years ago Permalink
Shouldn't it be:
UPDATE "master"."dbo"."spt_monitor" SET "connections"=18 WHERE  "lastrun"='2012-02-10 21:02:09';


(Space between date and time)

ansgar's profile image ansgar posted 9 years ago Permalink
Oh, I'll check that. I already wondered why that space is missing in your first post.
igitur's profile image igitur posted 9 years ago Permalink
I see I also left out a space in my original post at the top. Sorry about that. Use
yyyy-MM-dd HH:mm:ss.sss
ansgar's profile image ansgar posted 9 years ago Permalink
That does not return an error now:
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).
igitur's profile image igitur posted 9 years ago Permalink
Does this return 0 or 1?
select isdate('2012-02-10 21:02:09')


I believe it should return 1.
ansgar's profile image ansgar posted 9 years ago Permalink
Yes, that returns 1.
igitur's profile image igitur posted 9 years ago Permalink
Then the date format works.

If the WHERE clause doesn't work, then you don't have a row in spt_monitor with that exact date and time.
ansgar's profile image ansgar posted 9 years ago Permalink
The only row in that table has exactly this date/time value:
1 attachment(s):
  • datetime-mssql
ansgar's profile image ansgar posted 9 years ago Permalink
Here's the CREATE code for reproduction reasons:
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")
)
;
igitur's profile image igitur posted 9 years ago Permalink
Hmm, although not impossible, I find it very unlikely that there are no milliseconds in that field. Are you sure the full datetime value, including milliseconds, is showing?

Compare this example of someone else:
ansgar's profile image ansgar posted 9 years ago Permalink
You're right - it has a microseconds part, but HeidiSQL is not able to display it, even when I show me the raw data. Here's a screenshot of SQL management studio:

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';
1 attachment(s):
  • datetime-mssql-studio
igitur's profile image igitur posted 9 years ago Permalink
I'm really confident that if the isdate() function is recognising it as a date, then it should work.

What do you get for this?
select datediff(ms, lastrun, '2012-02-10 21:02:09.093') * 10000 TimeDiff from master.dbo.spt_monitor;


ansgar's profile image ansgar posted 9 years ago Permalink
That return 0 here.

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.
igitur's profile image igitur posted 9 years ago Permalink
I'm confused too. That's a system table. Are you sure it's writable?
ansgar's profile image ansgar posted 9 years ago Permalink
No clue. I did the same in a test table in a self-created schema:
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:
1 attachment(s):
  • datetime-mssql-studio2
igitur's profile image igitur posted 9 years ago Permalink
OK, seems I was wrong. According to http://stackoverflow.com/a/10818915 we should use yyyyMMdd instead of yyyy-MM-dd
igitur's profile image igitur posted 9 years ago Permalink
Does this work for you?

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
ansgar's profile image ansgar posted 9 years ago Permalink
I just executed that script in HeidiSQL.

None of the UPDATEs did change the "Tag" values.

Then I executed the UPDATEs in MSSQL Management Studio, again, with no effective change to the Tag values.
1 attachment(s):
  • datetime-mssql-studio3
ansgar's profile image ansgar posted 9 years ago Permalink
Does that all work on your server?
igitur's profile image igitur posted 9 years ago Permalink
Yup, works perfectly on mine. I'll look into this more.

What is your server's locale?
ansgar's profile image ansgar posted 9 years ago Permalink
Suddenly, this works here now:
UPDATE TestDates2 SET Tag = 'T3a' WHERE DateAndTimeWithMilliSeconds = '2013-03-12 03:08:17.449';


Only, HeidiSQL does not get the microseconds into the grid.
igitur's profile image igitur posted 9 years ago Permalink
Try the format with the T between the date and time, as discussed here :
http://stackoverflow.com/questions/5815954/independent-format-for-a-string-representation-of-date-time-value-for-ms-sql-se
ansgar's profile image ansgar posted 9 years ago Permalink
That "T" format also does not update anything here. Only the ISO format with a space inbetween works for updates, but not for the table which had a datetime field as primary key. And I can't get the microseconds to show up. This is all so weird...
igitur's profile image igitur posted 9 years ago Permalink
What is your Windows and SQL Server's version, locale and language? Let me see if I can replicate your setup.
ansgar's profile image ansgar posted 9 years ago Permalink
It's a german Windows 7 professional with SQL Server Express 11.0.2100.
[expired user #8745]'s profile image [expired user #8745] posted 9 years ago Permalink
Hi there. If I might chime in, two weeks later:

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:

2005:
"9,00,1406,00"	"2015-12-31 23:59:59"	"2016-01-01"


2008:
"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')


2005:
SQL Error (243): Type DATETIME2 is not a defined system type.


2008:
"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.
ansgar's profile image ansgar posted 9 years ago Permalink
Thanks, Ishmaeel. This really sheds more light on this mysterious topic.

As a conclusion, HeidiSQL should always use the following ISO format: '2012-02-10 21:02:09'
Right...?
[expired user #8745]'s profile image [expired user #8745] posted 9 years ago Permalink
Correct. Preferably with added 'T' designator.

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.
igitur's profile image igitur posted 9 years ago Permalink
Thanks from my side too, Ishmaeel. I was pulling my hair our and ended up setting up my own SQL server in German locale, but still couldn't reproduce the problems.
ansgar's profile image ansgar posted 9 years ago Permalink
I'm crossing my fingers that this will be a final fix.
[expired user #8745]'s profile image [expired user #8745] posted 9 years ago Permalink
I'll be happy to assist you with any tests you might need. Every dev/team needs a tester who uses Turkish settings. We are good at breaking stuff. happy
Code modification/commit from ansgar.becker, 9 years ago, revision 4912
Use ISO 8601 date/time format on MSSQL. See http://www.heidisql.com/forum.php?t=17728
ansgar's profile image ansgar posted 9 years ago Permalink
r4912 now uses ISO format without the "T" in the middle.

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?
igitur's profile image igitur posted 9 years ago Permalink
See http://stackoverflow.com/questions/14282802/how-to-update-a-datetime-on-sql-server-preserving-milliseconds-using-delphi-7-ad
Code modification/commit from ansgar.becker, 9 years ago, revision 4913
* 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
ansgar's profile image ansgar posted 9 years ago Permalink
r4913 should fix some things on MSSQL:
* 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.
igitur's profile image igitur posted 9 years ago Permalink
Confirmed that the new date format works for me. Thanks.

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