Hi,
I tried entering this value "8/24/2006 1:35:00.127 PM" with 127 as the milliseconds in a datetime field, but encountered error saying inconsistent datatype ...
Anyone knows how to store datetime value with milliseconds in the SQL database?
Thanks
All --
FYI, I found the issue that was causing me trouble.
It is a simple matter of display in Enterprise Manager.
When viewing data in Enterprise Manager using the GUI by right-clicking on a Table name and choosing "Return all rows" we see the data...
11/09/2006 15:38:14
...but, when we view the same data using Query Analyzer we see the data...
2006-11-09 15:38:13.907
...which is something quite different.
So, the value is stored with milliseconds but the value is NOT displayed in the same way using the GUI table-browser and Query Analyzer.
That was the crux of my issue.
(Imagine my surprise when I found out my code was actually working.)
:-)
Thank you.
-- Mark Kamoski
|||
If you want to store milliseconds you cannot use SmallDateTime because of limited resolution. The link below should take you in the right direction. Post again if you still have questions. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms187819.aspx
|||
Hi Caddre,
The datatype i am using is datetime and It's doesn't seem to allow me to add milliseconds to it. I am using sql 2000, but i don't think this have to do with SQL version.
|||
There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.
http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp
|||
thanks,
I managed to store milliseconds
INSERT INTO Table VALUES ({ts '2003-11-05 13:02:43.296'})
|||
The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.
|||
Motley:
The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.
I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?
I know that a SQL Sever 2000 datetime column can store 3 places of time accuracy, such as 12/30/2006 12:01:03.123, as noted here...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.asp
However, what is not clear is how to get a VS.NET 2003 DateTime variable value to insert into SQL Server datetime column with 3-places of accuracy.
I have tried several things, such as...
DateTime myDateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));
...but SQL Server always seems to trim the milliseconds.
What is the way to accomplish this?
Please advise.
Thank you.
-- Mark Kamoski
|||
Caddre:
There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.
http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp
I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?
The .NET data type DateTime has milliseconds but after I insert them into SQL Server the datetime column does not contain the milliseconds.
I am using ADO.NET, and OLEDB DataAdapter, and simply call Update passing a DataTable.Therefore, I just set the value in the DataTable as a .NET DateTime datatype with high millisecond precision and then call update. Unfortunately, the milliseconds are trimmed.
How can this be done?
|||
It is in the same area with the link you gave Motley. Read carefully and spend time with Katy kam’s blog she does this stuff for the BCL(base class library) team. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_1ckz.asp
http://blogs.msdn.com/kathykam/archive/2006/09/29/773041.aspx
|||
Mark,
If you tried the first post the link is to the general topic because the urls did not change, I have the SQL Server 2005 version but run a search for the topic in SQL Server 2000 BOL(books online) . Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms190234.aspx
|||
Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)
Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)
cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now
conn.open
cmd.executenonquery
conn.close
|||
Motley:
Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)
Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)
cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now
conn.open
cmd.executenonquery
conn.close
Motley --
OK. that might work.
I appreciate the clarification.
However, now that I think about it, I want to have as much resolution as possible to the date-time value being stored.
As such, if I want to store the full 7-digits to the right of the decimal point, (the resolution that .NET provides), then I think that I need to store the date as an nvarchar in some known format that can easily be parsed. When retrieving such values, I will have to cast from the SqlServer nvarchar to a .NET DateTime. That will make SQL-based comparisons/mining tricky. However, once the data is in-memory, in .NET code, and properly cast back to a DateTime, such comparison/mining will be simple. That seems to be a decent solution, at least in my case. And so on.
If anyone has a better solution, then please post it.
Thank you.
-- Mark Kamoski
|||
Motley:
Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)
Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)
cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now
conn.open
cmd.executenonquery
conn.close
Please help.
When I go to SQL Server 2000 Enterprise Manager, select a table, and choose "Return All Rows", and then try to enter...
01/01/2001 01:01:01.123
...I get the following error...
The value you entered is not consistent with the data type or length of the column, or over grid buffer limit
...which is strange.
I can enter the data...
01/01/2001 01:01:01.123
...but it will NOT allow entry of milliseconds.
Why?
Oddly enough, it will allow...
update ProgramLog set DateTimeLogged = '01/01/2001 01:02:03.456'
...and it enters the milliseconds but (another interesting point) it rounded-up ".456" to ".457".
SideBar -- Note that SQL Server 2000 Books Online says: "datetime - Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table."
Please advise.
Thank you.
-- Mark Kamoski
Greg Y wrote: