This is one of the strangest things I’ve seen lately, but it turns out that sending “2006-04-25 14:15:26:421” (via SqlParameter from SqlDbType.DateTime) to my Sql Server 2005 database has been saved as “2006-04-25 14:15:26:423“. Does it happen every time you might ask? well, the sad answer is NO; Sometimes(really, just sometimes) it happen and sometimes it don’t.
Why do I need such accuracy? Well, this DateTime column is one of the Primary Keys in my table. Trying to pull out “2006-04-25 14:15:26:421” will give me… nothing !
I wanted to reproduce it on a clean sheet so I created a new table named “CreationDates” with the single column “CreationDate” which is from DateTime type of course.
Then I’ve written the following code:
class Program
{
static void Main(string[] args)
{
Init();
Test();
}
private static void Init()
{
// Default culture – hebrew (I need it this way).
CultureInfo israel = new CultureInfo(“he-il”);
israel.DateTimeFormat.LongTimePattern = “HH:mm:ss.fff”;
Thread.CurrentThread.CurrentCulture = israel;
Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;
}
private static void Test()
{
string connString = @”MyConnectionString”;
using (SqlConnection conn = new SqlConnection(connString))
{
string query = “INSERT INTO CreationDates(CreationDate)VALUES(@CreationDate)”;
SqlCommand cmd = new SqlCommand(query, conn);
DateTime dt = DateTime.Now;
SqlParameter p1 = new SqlParameter(“CreationDate”, SqlDbType.DateTime);
p1.Value = dt;
cmd.Parameters.Add(p1);
Console.WriteLine(“parameter time: “ + ((DateTime)p1.Value).ToString());
conn.Open();
cmd.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connString))
{
string sQuery = “SELECT TOP(1) CreationDate FROM CreationDates ORDER BY CreationDate DESC”;
SqlCommand sCmd = new SqlCommand(sQuery, conn);
conn.Open();
SqlDataReader reader = sCmd.ExecuteReader();
if (reader.Read())
{
Console.WriteLine(“db time: “ + reader.GetDateTime(0).ToString());
}
}
}
}
* I know, I could make it nicer, but this is a simple test for god sakes !
In one of my tests, this code produce the following output:
The Sql Server 2005 Profile shows:
exec sp_executesql N’INSERT INTO CreationDates(CreationDate)VALUES(@CreationDate)’,N’@CreationDate datetime’,@CreationDate=”2006-04-25 14:15:26:423”
So I’ve sent “2006-04-25 14:15:26:421” but the database received “2006-04-25 14:15:26:423” !
Any smart ideas ??
