Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Forum Statistics

Users
Members:
1878
Members Online:
2
Guests Online:
55

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
1
New Topics:
0
Active Topics:
1

Our newest member
kowilmar1990z

Other

FAQ
All times are UTC [ DST ]

Google Ads

DateTime2 vs Datetime - new rule?

Please wait...

DateTime2 vs Datetime - new rule?

Postby lareau on Mon Jul 30, 2012 6:05 pm

Hi,

One of the dbas over here mentioned that I should be using Datetime2 instead of DateTime on my sql 2008R2 database.

Here's the documentation they send me
http://technet.microsoft.com/en-us/libr ... 87819.aspx

which mentions
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

This might be a idea for a new rule? similar to the text -> varchar(max) rule?

Have a great day,
Alex
lareau
Apprentice
Apprentice
LTD Bronze - Rating: 1
 
Posts: 20
Joined: Fri Oct 15, 2010 7:11 pm

Re: DateTime2 vs Datetime - new rule?

Postby SQLDenis on Mon Jul 30, 2012 6:37 pm

The reason I like datetime2 is that I can go back before 1753 in time
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: DateTime2 vs Datetime - new rule?

Postby gmmastros on Mon Jul 30, 2012 6:50 pm

That's not a bad suggestion. However, before doing this, I would prefer to find a way to check for instances where converting to DateTime2 would cause an error.

You can add and subtract numbers from a DateTime, but you cannot with a DateTime2.

Ex:

  1. Declare @Now DateTime
  2.  
  3. Set @Now = GetDate()
  4.  
  5. Select @Now + 1



Using DateTime2 causes and error:
  1. Declare @Now DateTime2
  2.  
  3. Set @Now = GetDate()
  4.  
  5. Select @Now + 1
  6.  


Msg 206, Level 16, State 2, Line 5
Operand type clash: datetime2 is incompatible with int


Before adding this check to SQLCop, I would like to add a check to see if there are any instances in code where doing so would cause the error.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm

Re: DateTime2 vs Datetime - new rule?

Postby SQLDenis on Mon Jul 30, 2012 6:58 pm

gmmastros wrote:
Msg 206, Level 16, State 2, Line 5
Operand type clash: datetime2 is incompatible with int




Correct, I also blogged about it here for the date data type http://blogs.lessthandot.com/index.php/ ... ible--2008

This is clearer also to a person who might not know what +1 means and it works on date, datetime and datetime2

  1. declare @d date
  2. select @d = getdate()
  3.  
  4. select @d  = dateadd(day,1,@d)
  5. select @d
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: DateTime2 vs Datetime - new rule?

Postby gmmastros on Mon Jul 30, 2012 8:35 pm

Denis,

I agree that DateTime2 is better, and that it's more obvious to use DateAdd. However, from a SQLCop perspective, I'd like to have a test that checks for date manipulation (using +1, etc...) before I add a check for DateTime vs. DateTime2.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm