We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

DATETIME vs DATETIME2

dbaSQL
dbaSQL asked
on
Medium Priority
1,722 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?
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
yes, that's still the same.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I would use DATETIME2, as accuracy is of concern

Author

Commented:
agreed.  thank you, angellll
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

Author

Commented:
I've seen the 1st one, but I'll check out the latter.  Thanks much.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.