Datetime comparison problem

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))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:
>
>
"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))
>
>|||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 so
urce to the times created by a Intel based computer. For example, an automat
ed process control system have be recording times that are .003 seconds 'of
f' 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...[
color=darkred]
> 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 wh
at
> 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))
>
>
>
>[/color]|||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" <arnie@.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))
>
>|||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.
"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" <arnie@.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...
>|||Arnie Rowland (arnie@.1568.com) writes:
> Due to a design flaw with Intel based chips, compute clock time is > accurate to t
he 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/online...=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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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" <arnie@.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.|||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
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97EC62C58ABBYazorman@.127.0.0.1...
> Arnie Rowland (arnie@.1568.com) writes:
>
> 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/online...=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, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||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 tim
e
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_harvey@.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:
>|||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
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 tim
e
>but the query afterwards. If they are stored inaccurately, I should still b
e
>able to use that query, unless it is stored internally pre-rounding. I stil
l
>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_harvey@.snet.net> wrote in message
> news:u21p9299dg97vdtin85imfs525fkefinc7@.
4ax.com...
>