0

I have a table with datetime2(7) column.

I create command parameters like this:

cmdInsertActionNote.Parameters.Add("@DateTimeUTC", SqlDbType.DateTime2);

And I populate and execute command like:

cmdInsertActionNote.Parameters["@DateTimeUTC"].Value = DateTime.utcNow;
int rowsAffected = cmdInsertActionNote.ExecuteNonQuery();

In my sql table I see:

2016-12-01 13:53:13.0700000

So I have milliseconds, but only ever 0 for last 4 digits.

What am I doing incorrectly?

EDIT 1:

For those who think it is a windows issue - it is NOT for me I have windows Server 2008 and I get microseconds. Here is a "time" column value populated from c#/datetime.utcNow.TimeOfDay:

12:06:56.6550029

ManInMoon
  • 6,795
  • 15
  • 70
  • 133
  • Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackoverflow.com/rooms/129570/discussion-on-question-by-maninmoon-not-getting-microseconds-when-i-insert-datet). – Russia Must Remove Putin Dec 01 '16 at 18:54

1 Answers1

1

From https://stackoverflow.com/a/13909323/563088

The values you'll get from DateTime.Now are only precise to within 16 milliseconds

See also: https://msdn.microsoft.com/en-us/library/system.datetime.utcnow(v=vs.110).aspx#Remarks

So the datetime2 might be able to hold micros but the code is feeding it millis

Community
  • 1
  • 1
Emond
  • 50,210
  • 11
  • 84
  • 115
  • The OP is using `DateTime.UtcNow`, not `DateTime.Now`, which does have different characteristics. – Maarten Dec 01 '16 at 14:16
  • @Maarten it's the same data type, only the timezone is different – Panagiotis Kanavos Dec 01 '16 at 14:16
  • @PanagiotisKanavos I know, but the precision is different. – Maarten Dec 01 '16 at 14:17
  • @Maarten no it isn't. You can't even specify microseconds in code, there is no such constructor *or* backing field. – Panagiotis Kanavos Dec 01 '16 at 14:19
  • @PanagiotisKanavos Who mentioned microseconds? Read the link that was added in the answer and you'll see there is a difference in **the precision/resolution of the value** of `DateTime.UtcNow`. This link: https://msdn.microsoft.com/en-us/library/system.datetime.utcnow(v=vs.110).aspx#Remarks – Maarten Dec 01 '16 at 14:21
  • The OP is asking about *microseconds*. The link you posted doesn't say that the precision is *different*. And the [source says that Now returns UtcNow](https://referencesource.microsoft.com/#mscorlib/system/datetime.cs,908) adjusted to the local timezone. The precision is exactly the same – Panagiotis Kanavos Dec 01 '16 at 14:26
  • @ManInMoon that doesn't prove anything. DateTime stores timer ticks, not milli or microseconds. If you want to really see what's going on, use SQL Server Profiler to capture the statement that gets executed and check the parameter value – Panagiotis Kanavos Dec 01 '16 at 14:30
  • I agree with @ManInMoon. According to the comments in the [source for datetime.cs](https://referencesource.microsoft.com/#mscorlib/system/datetime.cs,03ba40168be1388d), `DateTime` is stored using an `Int64` and is "the number of 100 nanosecond intervals since 12:00 AM January 1, year 1 A.D. in the proleptic Gregorian Calendar." So I'm not sure why the docs for `DateTime.UtcNow` specify 10ms. – Chris Dunaway Dec 01 '16 at 16:12
  • 1
    Looking further, the docs for `DateTime.Now` states this: "The resolution of this property depends on the system timer, which is approximately 15 milliseconds on Windows systems". So it appears that a `DateTime` structure can store up to 100ns precision, but `DateTime.Now` (and `DateTime.UtcNow`) are limited by the system timer. – Chris Dunaway Dec 01 '16 at 16:19