DATETIME vs DATETIME2

SQL 2008, one of the big ones, as far as I am concerned, is the newly accurate datetime datatypes  (ie datetime2, date, time, etc.)

I understand we've got precision to 100 nanoseconds with DATETIME2.  
I wasn't sure about DATETIME itself, and whether it still has an accuracy of 3.33 milliseconds.

Does it?
LVL 17
dbaSQLAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes, that's still the same.
0
 
dbaSQLAuthor Commented:
I should have been a little more detailed.  I am in the process of partioning some tables by datetime.  The function is DATETIME, but the table def is DATETIME2 as I am hoping to avoid the inaccuracies we have with v2000, and quit rounding the dates.

So I'm trying to determine if both the function and the table should be DATETIME, or DATETIME2.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would use DATETIME2, as accuracy is of concern
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
dbaSQLAuthor Commented:
agreed.  thank you, angellll
0
 
Daniel WilsonCommented:
just some links to back up what AngelIII says:
http://msdn.microsoft.com/en-us/library/ms187819.aspx  -- datetime "Accuracy                        -- Rounded to increments of .000, .003, or .007 seconds"

Discussion of DateTime, DateTime2, Date, and Time:
http://www.databasejournal.com/features/mssql/article.php/3718086/New-datetime-datatypes-in-SQL-Server-2008.htm
0
 
dbaSQLAuthor Commented:
I've seen the 1st one, but I'll check out the latter.  Thanks much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.