Reputation: 1428
According to this link http://msdn.microsoft.com/en-us/library/ms172424.aspx SQL Server Compact Edition 4.0 supports DateTime
with the usual SQL Server precision (seconds and milliseconds to 3.33).
However I'm creating records using the SQL Server CE ADO.NET provider and DateTime
but the data is only being stored to minute precision.
I've created SQL to return the data with DataReader.GetDateTime(x)
and the value is being returned without seconds or milliseconds.
Does SQL Server Compact 4.0 actually support lower precision?
My DDL is:
CREATE TABLE [Message]
(
[Id] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
[CreatedTimestamp] DATETIME NOT NULL,
[ProcessedTimestamp] DATETIME NOT NULL,
[QueueName] NVARCHAR(255) NOT NULL,
[MessageType] NVARCHAR(512) NOT NULL,
[MessageContent] NTEXT NOT NULL,
[MessageState] INT NOT NULL DEFAULT 0
);
My code is:
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
connection.Open();
using (SqlCeCommand command = new SqlCeCommand(
"INSERT INTO Message " +
"(Id, CreatedTimestamp, ProcessedTimestamp, QueueName, MessageType, MessageContent)" +
"VALUES " +
"(@Id, @CreatedTimestamp, @ProcessedTimestamp, @QueueName, @MessageType, @MessageContent)", connection))
{
command.Parameters.Add(new SqlCeParameter("@Id", message.MessageId) { DbType = DbType.Guid });
command.Parameters.Add(new SqlCeParameter("@CreatedTimestamp", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.fff")) { DbType = DbType.DateTime });
command.Parameters.Add(new SqlCeParameter("@ProcessedTimestamp", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.fff")) { DbType = DbType.DateTime });
command.Parameters.Add(new SqlCeParameter("@QueueName", queue) { DbType = DbType.String });
//Code snipped...
command.ExecuteNonQuery();
}
}
Upvotes: 1
Views: 1040
Reputation: 9
I had similar problem. The reason for me was in using SDF Viewer for exploring database. This application doesn't display seconds and milliseconds in datetime fields. But SQL Server Compact 4.0 actually stores datetime with high precision, as it's documented.
This question is old, but maybe my answer would be useful for someone.
Upvotes: 0