inserting DateTime in Sql Server

  • Thread starter Thread starter Peter K
  • Start date Start date
P

Peter K

Hi

When I insert a DateTime value to a database, and read it back again, it
appears that some of the data I wrote is missing.

For example, if I insert a DateTime, with a time portion of 16:24:12.421
(that is, 4:24pm and 12.421 seconds), when I read it back I get 4:24pm and
12.420 seconds. It seems there is a millisecond missing.

Is this a known "issue", or have I likely something else wrong in my
program?

Thanks,
Peter
 
Peter K said:
When I insert a DateTime value to a database, and read it back again, it
appears that some of the data I wrote is missing.
[...] It seems there is a millisecond missing.

Is this a known "issue"
Click to expand...

Yes, it is known. Take a look at the SQL Server documentation here:
http://msdn.microsoft.com/en-us/library/ms187819.aspx
Note the following: "datetime values are rounded to increments of .000,
..003, or .007 seconds, as shown in the following table..."
 
Mark Rae said:
Almost certainly.

But, since you haven't shown your code, it's impossible to tell...
Click to expand...

Hi, here is a snippet which shows the phenomenon I see.

// A date time (with a date, and a time including 421 ms):
DateTime datetime1 = new DateTime(2010, 1, 13, 16, 2, 23, 421);
DateTime? datetime2 = null;

using (IDbConnection conn = OpenDbConnection(connectionString))
{
using (IDbCommand command = conn.CreateCommand())
{
command.CommandText = "insert into STime (servername, utc) values
(@servername,@utc)";
command.CommandType = CommandType.Text;

IDbDataParameter servernameParameter = command.CreateParameter();
servernameParameter.ParameterName = "servername";
servernameParameter.DbType = DbType.String;
servernameParameter.Direction = ParameterDirection.Input;
servernameParameter.Value = "MY_SERVER";
command.Parameters.Add(servernameParameter);

IDbDataParameter datetimeParameter = command.CreateParameter();
datetimeParameter.ParameterName = "utc";
datetimeParameter.DbType = DbType.DateTime;
datetimeParameter.Direction = ParameterDirection.Input;
datetimeParameter.Value = datetime1;
command.Parameters.Add(datetimeParameter);

command.ExecuteNonQuery();
}

using (IDbCommand command = conn.CreateCommand())
{
command.CommandText = "select * from STime where
servername='MY_SERVER'";
command.CommandType = CommandType.Text;

using (IDataReader dataReader = command.ExecuteReader())
{
if (dataReader.Read())
{
datetime2 = dataReader.GetDateTime(1) as DateTime?;
}
}
}
}

After executing the above, "datetime2" contains the values I expect, except
for the milliseconds, which is 420, not 421.

What is incorrect?


Thanks,
Peter
 

Hi again - if I store "ticks" in the database, and read them back again (and
instantiate a new DateTime), then I get the behaviour I want (retaining the
millisecond value).

So I'll just go that way. Luckily I can set up the database table as I like.

Thanks,
Peter
 
Alberto Poblacion said:
Peter K said:
When I insert a DateTime value to a database, and read it back again, it
appears that some of the data I wrote is missing.
[...] It seems there is a millisecond missing.

Is this a known "issue"
Click to expand...

Yes, it is known. Take a look at the SQL Server documentation here:
http://msdn.microsoft.com/en-us/library/ms187819.aspx
Note the following: "datetime values are rounded to increments of .000,
.003, or .007 seconds, as shown in the following table..."
Click to expand...

OK - thanks very much for the info. In my totally naive opinion, this does
not seem "perfect".

As the article states, if I try to insert a DateTime like 2010-1-13
23:59:59.999 (ie the 13th of January 2010, at 23:59:59.999), then the
database will actually store the date as the 14th of January. So I guess
it's best to "sanitise" any dates I get before inserting them in the DB (eg
remove the millisecond part?).

/Peter
 
Alberto Poblacion said:
Peter K said:
When I insert a DateTime value to a database, and read it back again, it
appears that some of the data I wrote is missing.
[...] It seems there is a millisecond missing.

Is this a known "issue"
Click to expand...

Yes, it is known. Take a look at the SQL Server documentation here:
http://msdn.microsoft.com/en-us/library/ms187819.aspx
Note the following: "datetime values are rounded to increments of .000,
.003, or .007 seconds, as shown in the following table..."
Click to expand...

OK - thanks very much for the info. In my totally naive opinion, this does
not seem "perfect".

As the article states, if I try to insert a DateTime like 2010-1-13
23:59:59.999 (ie the 13th of January 2010, at 23:59:59.999), then the
database will actually store the date as the 14th of January. So I guess
it's best to "sanitise" any dates I get before inserting them in the DB (eg
remove the millisecond part?).
Click to expand...

Any fixed precision will give rounding errors when the precision
is exceeded.

That is just how it is.

I don't think this particular precision is a natural one.

But Sybase (!) made that decision sometimes back in late 1980's.

Arne
 
Ah, hence the confusion...

When people don't explicitly say which version of anything they are
using, I always assume that they are using the current version...
Click to expand...

Sometimes they are not.

Not even with .NET / VS.

And definitely not with SQLServer.

Database upgrades is often avoided until absolutely
necessary.

Arne
 
Back
Top