SQL Server date/time oddity.
-
I run a query in SSMS. A date/time column displays correctly like "2013-12-15 12:19:43.583". I get the data table object back into my app and iterate through the data rows. Converting the column using DateTime always "loses" the millisecond component. I tried converting it to a string just to see if it was coming through that way, but no, the millisecond is always "lost". I then changed the query to cast the datetime column into two columns, one for the date and one for the time. Converting the time column to a DateTime now correctly contains the millisecond component. If the column in the database is defined as datetime why am I not getting the millsecond component? There's no problem converting the value in the row using DateTime but the loss of the millsecond puzzles me because I thought a database datetime column would be wholly compatible with a Dotnet DateTime object? Are my pudgy fingers, and thus by association, my pudgy brain, doing something wrong in my code?
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
-
I run a query in SSMS. A date/time column displays correctly like "2013-12-15 12:19:43.583". I get the data table object back into my app and iterate through the data rows. Converting the column using DateTime always "loses" the millisecond component. I tried converting it to a string just to see if it was coming through that way, but no, the millisecond is always "lost". I then changed the query to cast the datetime column into two columns, one for the date and one for the time. Converting the time column to a DateTime now correctly contains the millisecond component. If the column in the database is defined as datetime why am I not getting the millsecond component? There's no problem converting the value in the row using DateTime but the loss of the millsecond puzzles me because I thought a database datetime column would be wholly compatible with a Dotnet DateTime object? Are my pudgy fingers, and thus by association, my pudgy brain, doing something wrong in my code?
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
Thankfully in all my years as a LOB developer I have not once had to utilise the millisecond information so I have no idea :-D I'm assuming this is an intellectual exercise only!
Never underestimate the power of human stupidity RAH
-
I run a query in SSMS. A date/time column displays correctly like "2013-12-15 12:19:43.583". I get the data table object back into my app and iterate through the data rows. Converting the column using DateTime always "loses" the millisecond component. I tried converting it to a string just to see if it was coming through that way, but no, the millisecond is always "lost". I then changed the query to cast the datetime column into two columns, one for the date and one for the time. Converting the time column to a DateTime now correctly contains the millisecond component. If the column in the database is defined as datetime why am I not getting the millsecond component? There's no problem converting the value in the row using DateTime but the loss of the millsecond puzzles me because I thought a database datetime column would be wholly compatible with a Dotnet DateTime object? Are my pudgy fingers, and thus by association, my pudgy brain, doing something wrong in my code?
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
Nothing to see here. :sigh: Are you using a datareader? If so, using
DateTime myDate = (DateTime)reader["MyDateTimeColumn"];will convert the DateTime from the DB into a string and then convert it back to DateTime, and thus losing precision. Instead usereader.GetDateTime(reader.GetOrdinal("MyDateTimeColumn"));<edit> Instead useDateTime myDate = (DateTime)reader.GetSQLDateTime(reader.GetOrdinal("MyDateTimeColumn"));orDateTime myDate = (DateTime)reader.GetSQLValue(reader.GetOrdinal("MyDateTimeColumn"));</edit> Otherwise you need to update the question with a bit of code.Wrong is evil and must be defeated. - Jeff Ello[^]
-
Nothing to see here. :sigh: Are you using a datareader? If so, using
DateTime myDate = (DateTime)reader["MyDateTimeColumn"];will convert the DateTime from the DB into a string and then convert it back to DateTime, and thus losing precision. Instead usereader.GetDateTime(reader.GetOrdinal("MyDateTimeColumn"));<edit> Instead useDateTime myDate = (DateTime)reader.GetSQLDateTime(reader.GetOrdinal("MyDateTimeColumn"));orDateTime myDate = (DateTime)reader.GetSQLValue(reader.GetOrdinal("MyDateTimeColumn"));</edit> Otherwise you need to update the question with a bit of code.Wrong is evil and must be defeated. - Jeff Ello[^]
Jörgen Andersson wrote:
(DateTime)reader["MyDateTimeColumn"];will convert the DateTime from the DB into a string and then convert it back to DateTimeEr, no it won't. It will take the
DateTimevalue, box it as anObject, and then un-box it to aDateTime. At no point will the value be converted to or from a string.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I run a query in SSMS. A date/time column displays correctly like "2013-12-15 12:19:43.583". I get the data table object back into my app and iterate through the data rows. Converting the column using DateTime always "loses" the millisecond component. I tried converting it to a string just to see if it was coming through that way, but no, the millisecond is always "lost". I then changed the query to cast the datetime column into two columns, one for the date and one for the time. Converting the time column to a DateTime now correctly contains the millisecond component. If the column in the database is defined as datetime why am I not getting the millsecond component? There's no problem converting the value in the row using DateTime but the loss of the millsecond puzzles me because I thought a database datetime column would be wholly compatible with a Dotnet DateTime object? Are my pudgy fingers, and thus by association, my pudgy brain, doing something wrong in my code?
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
How are you inspecting the value of the column? The debugger is probably using the default
.ToStringmethod, which will not display the full value. If you use the format string"yyyy-MM-dd HH:mm:ss.fff", you should see the full value.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Jörgen Andersson wrote:
(DateTime)reader["MyDateTimeColumn"];will convert the DateTime from the DB into a string and then convert it back to DateTimeEr, no it won't. It will take the
DateTimevalue, box it as anObject, and then un-box it to aDateTime. At no point will the value be converted to or from a string.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Correct me if I'm wrong here (it's quite possible) But assuming SQLServer, it will return a SQLDateTime value, as GetValue will only return dotnet types the SQLDateTime will be internally converted to a dotnet type. The Operator for converting from SQLDateTime to DateTime is an Explicit Operator, and as far as I know this internal conversion will not use explicit operators, so it will be an implicit conversion to String instead. The solution is to either use GetSQLValue or GetSQLDateTime or GetDateTime. <edit>Apparently not GetDateTime either.</edit>
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Correct me if I'm wrong here (it's quite possible) But assuming SQLServer, it will return a SQLDateTime value, as GetValue will only return dotnet types the SQLDateTime will be internally converted to a dotnet type. The Operator for converting from SQLDateTime to DateTime is an Explicit Operator, and as far as I know this internal conversion will not use explicit operators, so it will be an implicit conversion to String instead. The solution is to either use GetSQLValue or GetSQLDateTime or GetDateTime. <edit>Apparently not GetDateTime either.</edit>
Wrong is evil and must be defeated. - Jeff Ello[^]
No, the value will not be converted to a string. Tracing through the code with Reflector, the
GetDateTimemethod returns the value of theSqlBuffer.DateTimeproperty. The indexer calls theGetValuemethod, which returns the value of theSqlBuffer.Valueproperty, which contains aswitchblock based on the type returned from the database. For thedatetimetype, it returns the value of theSqlBuffer.DateTimeproperty.GetSqlDateTimereturns the value of theSqlBuffer.SqlDateTimeproperty. BothSqlBuffer.SqlDateTimeandSqlBuffer.DateTimeconstruct the value from two integers returned by SQL. Neither property converts the value to a string. It might be slightly more efficient to callGetDateTimeif you know that the value is adatetimetype, but the indexer andGetValuemethod will return exactly the same value.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
No, the value will not be converted to a string. Tracing through the code with Reflector, the
GetDateTimemethod returns the value of theSqlBuffer.DateTimeproperty. The indexer calls theGetValuemethod, which returns the value of theSqlBuffer.Valueproperty, which contains aswitchblock based on the type returned from the database. For thedatetimetype, it returns the value of theSqlBuffer.DateTimeproperty.GetSqlDateTimereturns the value of theSqlBuffer.SqlDateTimeproperty. BothSqlBuffer.SqlDateTimeandSqlBuffer.DateTimeconstruct the value from two integers returned by SQL. Neither property converts the value to a string. It might be slightly more efficient to callGetDateTimeif you know that the value is adatetimetype, but the indexer andGetValuemethod will return exactly the same value.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Had to download Reflector to check this. Also had to update my understanding about SqlDataReader. Tonight I'll eventually deepfry an author of an article, if I can find it back. :sigh:
Wrong is evil and must be defeated. - Jeff Ello[^]
