Datetime2 variables and columns have a precision of 100 nanoseconds. So if
you need an accuracy better than + or - 3 milliseconds or so, you need to
use datetime2.
> The precision of the datetime values is a little bigger then 3
> milliseconds (it's not exactly 3 milliseconds) so you cannot compare them
> with a greater precision then around 3 milliseconds:
> select convert (datetime, '2009-07-16 22:31:54.002)
> select convert (datetime, '2009-07-16 22:31:54.016)
> select convert (datetime, '2009-07-16 22:31:54.017)
> The precision for datetime2 is higher than that but I don't remember by
> how much.
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
> Independent consultant and remote programming for Access and SQL-Server
> (French)
> "Martin" <martin_remove_and_no_spam@martinz_remove_.co.nz> wrote in
> message news:6A6A3B67-1152-4DCD-9155-7D01A9BCB6CD@microsoft.com...
>> Hi,
>> I am wondering how to compare 2 datetime values so that milliseconds are
>> taken into account.
>> as an example I have the following script.
>> it appears that if I compare two datetime variables then milliseconds are
>> not taken into account, however if I compare two datatime2 variables then
>> milliseconds are taken into account.
>> I have put together the script below to demonstate the problem.
>> any help is appreciated.
>> thanks in advance.
>> cheers
>> martin.
>> USE [tempdb];
>> GO
>> declare @Date1 datetime = '2009-07-16 22:31:54.416';
>> declare @Date2 datetime = '2009-07-16 22:31:54.417';
>> declare @Date3 datetime2 = '2009-07-16 22:31:54.416';
>> declare @Date4 datetime2 = '2009-07-16 22:31:54.417';
>> /*Try comparing datetime datatype*/
>> print 'Compare datetime to datetime - Incorrect';
>> if @Date1 < @Date2
>> print 'Correct Answer'
>> Else
>> print 'Incorrect Answer'
>> /*Try comparing datetime2 datatype*/
>> print 'Compare datetime2 to datetime2 - correct';
>> if @Date3 < @Date4
>> print 'Correct Answer'
>> Else
>> print 'Incorrect Answer'
>> /*Try comparing datetime and datetime2 datatype*/
>> print 'Compare datetime2 to datetime2 - incorrect';
>> if @Date1 < @Date4
>> print 'Correct Answer'
>> Else
>> print 'Incorrect Answer'
>> print CONVERT(datetime2, @date1, 113); /*This will round incorrectly*/
>> print CONVERT(datetime2, @Date2, 113);
>> print @date3;
>> print @date4;
>> GO
|
