?
Solved

DATETIME vs DATETIME2

Posted on 2009-02-10
6
Medium Priority
?
1,661 Views
Last Modified: 2012-08-14
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?
0
Comment
Question by:dbaSQL
  • 3
  • 2
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 23601536
yes, that's still the same.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23601539
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23601585
I would use DATETIME2, as accuracy is of concern
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 17

Author Comment

by:dbaSQL
ID: 23601600
agreed.  thank you, angellll
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 23601668
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 23601795
I've seen the 1st one, but I'll check out the latter.  Thanks much.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question