Can't update DateTime column in MS SQL table

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;

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.

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.

Super. So I'll give it a try.

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

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)

Oh, I'll check that. I already wondered why that space is missing in your first post.

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

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

Does this return 0 or 1?

select isdate('2012-02-10 21:02:09')

I believe it should return 1.

Yes, that returns 1.

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.

The only row in that table has exactly this date/time value:

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

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:

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';

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;

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.

I'm confused too. That's a system table. Are you sure it's writable?

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:

OK, seems I was wrong. According to http://stackoverflow.com/a/10818915 we should use yyyyMMdd instead of yyyy-MM-dd

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

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.

Does that all work on your server?

Yup, works perfectly on mine. I'll look into this more.

What is your server's locale?

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.

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

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

What is your Windows and SQL Server's version, locale and language? Let me see if I can replicate your setup.

It's a german Windows 7 professional with SQL Server Express 11.0.2100.

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.

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

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.

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.

I'm crossing my fingers that this will be a final fix.

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.

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?

See http://stackoverflow.com/questions/14282802/how-to-update-a-datetime-on-sql-server-preserving-milliseconds-using-delphi-7-ad

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.

Confirmed that the new date format works for me. Thanks.