Datetime comparison problem

microsoft.public.sqlserver.programming

23 Jun 2006 5:32 PM

Ellie

Hi,

I am having trouble finding any records with a datetime of 2006-06-16
09:04:39:347. This time clearly falls between the beginning and end times.
I'm even converting to a char to see if that works. It seems to work if I
use .346 but sometimes it requires a difference of .003. Can anyone see what
I might be doing wrong? Thanks Ellie

declare @dPrevDate datetime
declare @dNextDate datetime

select @dPrevDate = '2006-06-16 09:04:39.346'
select @dNextDate = '2006-06-16 09:05:56.110'
Select *
where (convert(char, tblChanges.dateaction, 21) > convert(char,
@dPrevDate, 21) and
convert(char, tblChanges.dateaction, 21) < convert(char, @dNextDate,
21))

23 Jun 2006 5:47 PM

Ellie

Sorry I made typos, I should have said that it DIDN'T work for 346 but did
work for 345 (when 346 is clearly less than 347) and the code should be:
Show quote

>
>> declare @dPrevDate datetime
>> declare @dNextDate datetime
>>
>> select @dPrevDate = '2006-06-16 09:04:39.346'
>> select @dNextDate = '2006-06-16 09:05:56.110'
>> Select * from tblChanges
>> where (convert(char, tblChanges.dateaction, 21) > convert(char,
>> @dPrevDate, 21) and
>> convert(char, tblChanges.dateaction, 21) < convert(char,
>> @dNextDate, 21))
>>
>
"Ellie" <nospam@nospam.net> wrote in message
news:O5OF6rulGHA.3816@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what I might be doing wrong? Thanks Ellie
>
> declare @dPrevDate datetime
> declare @dNextDate datetime
>
> select @dPrevDate = '2006-06-16 09:04:39.346'
> select @dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @dNextDate, 21))
>
>
>
>

23 Jun 2006 5:58 PM

Arnie Rowland

Due to a design flaw with Intel based chips, compute clock time is accurate to the nearest three thousandth of a second.

This makes it difficult to coordinate precise times from a different time source to the times created by a Intel based computer. For example, an automated process control system have be recording times that are ±.003 seconds 'off' what the computer thinks.

You have to build in the ±.003 'fudge' factor.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Ellie" <nospam@nospam.net> wrote in message news:O5OF6rulGHA.3816@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see what
> I might be doing wrong? Thanks Ellie
>
> declare @dPrevDate datetime
> declare @dNextDate datetime
>
> select @dPrevDate = '2006-06-16 09:04:39.346'
> select @dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char, @dNextDate,
> 21))
>
>
>
>

23 Jun 2006 6:13 PM

Ellie

I was thinking that there must have been a problem there but why can't I
convert it to a string and have it more accurate? That isn't working either.

"Arnie Rowland" <ar***@1568.com> wrote in message
news:umCpU6ulGHA.4144@TK2MSFTNGP05.phx.gbl...

Due to a design flaw with Intel based chips, compute clock time is accurate
to the nearest three thousandth of a second.

This makes it difficult to coordinate precise times from a different time
source to the times created by a Intel based computer. For example, an
automated process control system have be recording times that are ±.003
seconds 'off' what the computer thinks.

You have to build in the ±.003 'fudge' factor.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Ellie" <nospam@nospam.net> wrote in message
news:O5OF6rulGHA.3816@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what
> I might be doing wrong? Thanks Ellie
>
> declare @dPrevDate datetime
> declare @dNextDate datetime
>
> select @dPrevDate = '2006-06-16 09:04:39.346'
> select @dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @dNextDate,
> 21))
>
>
>
>

23 Jun 2006 7:17 PM

Aaron Bertrand [SQL Server MVP]

I don't have any clue what "isn't working" means. However, I do have a
suggestion for storing accuracies under 3 ms.

When you enter the data to SQL Server, you can store the date +
minutes/seconds in datetime, then store *your* milliseconds in a separate
INT column. Or to save space you could use smalldatetime for date + minutes
and store seconds*1000+milliseconds in a separate INT column.

Now, when you query, you'll have something more complex to work with, and
that will be the harder part, but your data will be there. You can just
query for the times within your minute, and then where the milliseconds are
in the range you're after.

Show quote

"Ellie" <nospam@nospam.net> wrote in message
news:uTH03CvlGHA.884@TK2MSFTNGP05.phx.gbl...
>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working
>either.
>
>
> "Arnie Rowland" <ar***@1568.com> wrote in message
> news:umCpU6ulGHA.4144@TK2MSFTNGP05.phx.gbl...
> Due to a design flaw with Intel based chips, compute clock time is
> accurate to the nearest three thousandth of a second.
>
> This makes it difficult to coordinate precise times from a different time
> source to the times created by a Intel based computer. For example, an
> automated process control system have be recording times that are ±.003
> seconds 'off' what the computer thinks.
>
> You have to build in the ±.003 'fudge' factor.
>
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "Ellie" <nospam@nospam.net> wrote in message
> news:O5OF6rulGHA.3816@TK2MSFTNGP02.phx.gbl...
>> Hi,
>>
>> I am having trouble finding any records with a datetime of 2006-06-16
>> 09:04:39:347. This time clearly falls between the beginning and end
>> times.
>> I'm even converting to a char to see if that works. It seems to work if I
>> use .346 but sometimes it requires a difference of .003. Can anyone see
>> what
>> I might be doing wrong? Thanks Ellie
>>
>> declare @dPrevDate datetime
>> declare @dNextDate datetime
>>
>> select @dPrevDate = '2006-06-16 09:04:39.346'
>> select @dNextDate = '2006-06-16 09:05:56.110'
>> Select *
>> where (convert(char, tblChanges.dateaction, 21) > convert(char,
>> @dPrevDate, 21) and
>> convert(char, tblChanges.dateaction, 21) < convert(char,
>> @dNextDate,
>> 21))
>>
>>
>>
>>
>

24 Jun 2006 12:19 AM

Roy Harvey

You are seeing a limit of the DATETIME datatype in SQL Server. From
the Books on Line: "...to an accuracy of one three-hundredth of a
second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
are rounded to increments of .000, .003, or .007 seconds...."

So, to store the time more accurately you must, as Aaron explained,
have to "roll your own".

Roy Harvey
Beacon Falls, CT

Show quote

On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@nospam.net> wrote:

>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working either.
>
>
>"Arnie Rowland" <ar***@1568.com> wrote in message
>news:umCpU6ulGHA.4144@TK2MSFTNGP05.phx.gbl...
>Due to a design flaw with Intel based chips, compute clock time is accurate
>to the nearest three thousandth of a second.
>
>This makes it difficult to coordinate precise times from a different time
>source to the times created by a Intel based computer. For example, an
>automated process control system have be recording times that are ±.003
>seconds 'off' what the computer thinks.
>
>You have to build in the ±.003 'fudge' factor.

24 Jun 2006 11:48 AM

Ellie

So does this mean that internally they are not rounded (0.00333) but what
I'm seeing in query analyzer when I try to query it, is the rounded .000,
..003 or .007, etc.? I'm not concerned about the accurate storing of the time
but the query afterwards. If they are stored inaccurately, I should still be
able to use that query, unless it is stored internally pre-rounding. I still
don't get why I can't convert it to a string and do a string comparison
though. Hope I am making sense.

Thanks for all of your help. I'll read some of the links and work around
this.

Ellie

Show quote

"Roy Harvey" <roy_har***@snet.net> wrote in message
news:u21p9299dg97vdtin85imfs525fkefinc7@4ax.com...
> You are seeing a limit of the DATETIME datatype in SQL Server. From
> the Books on Line: "...to an accuracy of one three-hundredth of a
> second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
> are rounded to increments of .000, .003, or .007 seconds...."
>
> So, to store the time more accurately you must, as Aaron explained,
> have to "roll your own".
>
> Roy Harvey
> Beacon Falls, CT
>
> On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@nospam.net> wrote:
>
>>I was thinking that there must have been a problem there but why can't I
>>convert it to a string and have it more accurate? That isn't working
>>either.
>>
>>
>>"Arnie Rowland" <ar***@1568.com> wrote in message
>>news:umCpU6ulGHA.4144@TK2MSFTNGP05.phx.gbl...
>>Due to a design flaw with Intel based chips, compute clock time is
>>accurate
>>to the nearest three thousandth of a second.
>>
>>This makes it difficult to coordinate precise times from a different time
>>source to the times created by a Intel based computer. For example, an
>>automated process control system have be recording times that are ±.003
>>seconds 'off' what the computer thinks.
>>
>>You have to build in the ±.003 'fudge' factor.

24 Jun 2006 1:56 PM

Roy Harvey

Effectively they are rounded (as demonstrated below) and stored that
way. Comparison of a string against a datetime is performed by
comparing datetime types after converting the string. The string
conversion follows the rules demonstrated below.

Lets look at them as strings, and as strings converted to datetime:

select '2006-06-16 09:04:39.338',
       convert(datetime,'2006-06-16 09:04:39.338') UNION
select '2006-06-16 09:04:39.339',
        convert(datetime,'2006-06-16 09:04:39.339') UNION
select '2006-06-16 09:04:39.340',
        convert(datetime,'2006-06-16 09:04:39.340') UNION
select '2006-06-16 09:04:39.341',
        convert(datetime,'2006-06-16 09:04:39.341') UNION
select '2006-06-16 09:04:39.342',
        convert(datetime,'2006-06-16 09:04:39.342') UNION
select '2006-06-16 09:04:39.343',
        convert(datetime,'2006-06-16 09:04:39.343') UNION
select '2006-06-16 09:04:39.344',
        convert(datetime,'2006-06-16 09:04:39.344') UNION
select '2006-06-16 09:04:39.345',
        convert(datetime,'2006-06-16 09:04:39.345') UNION
select '2006-06-16 09:04:39.346',
        convert(datetime,'2006-06-16 09:04:39.346') UNION
select '2006-06-16 09:04:39.347',
        convert(datetime,'2006-06-16 09:04:39.347') UNION
select '2006-06-16 09:04:39.348',
        convert(datetime,'2006-06-16 09:04:39.348') UNION
select '2006-06-16 09:04:39.349',
        convert(datetime,'2006-06-16 09:04:39.349') UNION
select '2006-06-16 09:04:39.350',
        convert(datetime,'2006-06-16 09:04:39.350') UNION
select '2006-06-16 09:04:39.351',
        convert(datetime,'2006-06-16 09:04:39.351') UNION
select '2006-06-16 09:04:39.352',
        convert(datetime,'2006-06-16 09:04:39.352')
order by 1

2006-06-16 09:04:39.338     2006-06-16 09:04:39.337

2006-06-16 09:04:39.339     2006-06-16 09:04:39.340
2006-06-16 09:04:39.340     2006-06-16 09:04:39.340
2006-06-16 09:04:39.341     2006-06-16 09:04:39.340

2006-06-16 09:04:39.342     2006-06-16 09:04:39.343
2006-06-16 09:04:39.343     2006-06-16 09:04:39.343
2006-06-16 09:04:39.344     2006-06-16 09:04:39.343

2006-06-16 09:04:39.345     2006-06-16 09:04:39.347
2006-06-16 09:04:39.346     2006-06-16 09:04:39.347
2006-06-16 09:04:39.347     2006-06-16 09:04:39.347
2006-06-16 09:04:39.348     2006-06-16 09:04:39.347

2006-06-16 09:04:39.349     2006-06-16 09:04:39.350
2006-06-16 09:04:39.350     2006-06-16 09:04:39.350
2006-06-16 09:04:39.351     2006-06-16 09:04:39.350

2006-06-16 09:04:39.352     2006-06-16 09:04:39.353

I hope that helps.

Roy Harvey
Beacon Falls, CT

Show quote

On Sat, 24 Jun 2006 07:48:43 -0400, "Ellie" <nospam@nospam.net> wrote:

>So does this mean that internally they are not rounded (0.00333) but what
>I'm seeing in query analyzer when I try to query it, is the rounded .000,
>.003 or .007, etc.? I'm not concerned about the accurate storing of the time
>but the query afterwards. If they are stored inaccurately, I should still be
>able to use that query, unless it is stored internally pre-rounding. I still
>don't get why I can't convert it to a string and do a string comparison
>though. Hope I am making sense.
>
>Thanks for all of your help. I'll read some of the links and work around
>this.
>
>Ellie
>
>"Roy Harvey" <roy_har***@snet.net> wrote in message
>news:u21p9299dg97vdtin85imfs525fkefinc7@4ax.com...
>> You are seeing a limit of the DATETIME datatype in SQL Server. From
>> the Books on Line: "...to an accuracy of one three-hundredth of a
>> second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
>> are rounded to increments of .000, .003, or .007 seconds...."
>>
>> So, to store the time more accurately you must, as Aaron explained,
>> have to "roll your own".
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@nospam.net> wrote:
>>
>>>I was thinking that there must have been a problem there but why can't I
>>>convert it to a string and have it more accurate? That isn't working
>>>either.
>>>
>>>
>>>"Arnie Rowland" <ar***@1568.com> wrote in message
>>>news:umCpU6ulGHA.4144@TK2MSFTNGP05.phx.gbl...
>>>Due to a design flaw with Intel based chips, compute clock time is
>>>accurate
>>>to the nearest three thousandth of a second.
>>>
>>>This makes it difficult to coordinate precise times from a different time
>>>source to the times created by a Intel based computer. For example, an
>>>automated process control system have be recording times that are ±.003
>>>seconds 'off' what the computer thinks.
>>>
>>>You have to build in the ±.003 'fudge' factor.
>

23 Jun 2006 10:36 PM

Erland Sommarskog

Arnie Rowland (ar***@1568.com) writes:

> Due to a design flaw with Intel based chips, compute clock time is > accurate to the nearest three thousandth of a second.

> This makes it difficult to coordinate precise times from a different
> time source to the times created by a Intel based computer. For example,
> an automated process control system have be recording times that are
> ±.003 seconds 'off' what the computer thinks.

Eh? It's perfectly possible in Windows to handle time down to
dissolution of 100 ns, I believe. At least, you can get far below
3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
durations in µs.

In fact, if you look at
http://manuals.sybase.com:80/onlinebooks/group-as/asg1250e/refman/@Generic__BookTextView/2862;pt=2862;lang=sv#X
you can see that Sybase has the same accuracy. And, as may know
Microsoft SQL Server was originally derived from Sybase. And Sybase
has its origin on Unix. So I doubt that whereever the design flaw
was, that it was with Intel chips.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

24 Jun 2006 12:51 AM

Arnie Rowland

My brain checked out -it is a code artifact that predates Intel chip
design -is more in keeping of what I wanted to say. The latest IntelAMD
chips are capable of almost µs precision.

Erlund, Thanks for catching this.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns97EC62C58ABBYazorman@127.0.0.1...
> Arnie Rowland (ar***@1568.com) writes:
>> Due to a design flaw with Intel based chips, compute clock time is >
>> accurate to the nearest three thousandth of a second.
>
>> This makes it difficult to coordinate precise times from a different
>> time source to the times created by a Intel based computer. For example,
>> an automated process control system have be recording times that are
>> ±.003 seconds 'off' what the computer thinks.
>
> Eh? It's perfectly possible in Windows to handle time down to
> dissolution of 100 ns, I believe. At least, you can get far below
> 3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
> durations in µs.
>
> In fact, if you look at
> http://manuals.sybase.com:80/onlinebooks/group-as/asg1250e/refman/@Generic__BookTextView/2862;pt=2862;lang=sv#X
> you can see that Sybase has the same accuracy. And, as may know
> Microsoft SQL Server was originally derived from Sybase. And Sybase
> has its origin on Unix. So I doubt that whereever the design flaw
> was, that it was with Intel chips.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx