Question MSSQL datetime difference by 1ms when inserted.

[rhoaste]

I'm testing a vb.net application and have noticed that a datetime column I have inserted into the database differs by 1 millisecond...

CREATE TABLE [dbo].[dates](
[d] [datetime] NULL
) ON [PRIMARY]
- - - - - - - - - - - - - - - - - -
insert into dates (d) values('2009-02-06 17:27:47.312')
- - - - - - - - - - - - - - - - - -
select * from dates

result....
2009-02-06 17:27:47.313

The value returned is different to the value inserted by .001 sec

Why is this?

 

[rhoaste]

I've found the answer myself...

The problem is down to a constraint of the datetime datatype in MSSQL. The datetime datatype date range is from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds. I ended up using a string to maintain the accuracy of the input data as I dont need to perform any DB date related functions.

Thanks to everyone for the overwhelming response on this... As the dolphins will say someday in the distant future, "So long and thanks for all the fish".

 

[MattP]

Thanks to everyone for the overwhelming response on this...

That will get you a ton of responses in future threads /s.

You need to realize this is a community powered by individuals contributing their time and knowledge for free. If someone knows the answer they'll more than likely answer it for you assuming you've put some thought into the problem and ask politely. If no one knows the answer you may not get an immediate response, or none at all if it's a problem we haven't come across. If you come into the forum with an impolite attitude then you have a higher chance of being snubbed the next time you have an issue.

 

[rhoaste]

The "Chief" has spoken.

That will get you a ton of responses in future threads /s.

You need to realize this is a community powered by individuals contributing their time and knowledge for free. If someone knows the answer they'll more than likely answer it for you assuming you've put some thought into the problem and ask politely. If no one knows the answer you may not get an immediate response, or none at all if it's a problem we haven't come across. If you come into the forum with an impolite attitude then you have a higher chance of being snubbed the next time you have an issue.

Thank you. I asked nicely. I am polite. It's not a complicated question for a person that uses MSSQL all of the time. And until now, and have been patient. A community is all about sharing. If you have a community, which for all intents and purposes is a ghost town, then it's not worth a jot.

So with that in mind, I VERY MUCH DOUBT I'll be back here to provide or request any type of help or advice.

Now, please close my account. Thanks.

Have a nice day.