Persistence of Time... DateTime bugs, that is

Guess what? The DateTime rounding bug never got fixed in SQL Server
2005. You know, this one:
SELECT CAST('10/25/2005 23:59:59.990' AS DATETIME) AS [990]
SELECT CAST('10/25/2005 23:59:59.991' AS DATETIME) AS [991]
SELECT CAST('10/25/2005 23:59:59.992' AS DATETIME) AS [992]
SELECT CAST('10/25/2005 23:59:59.993' AS DATETIME) AS [993]
SELECT CAST('10/25/2005 23:59:59.994' AS DATETIME) AS [994]
SELECT CAST('10/25/2005 23:59:59.995' AS DATETIME) AS [995]
SELECT CAST('10/25/2005 23:59:59.996' AS DATETIME) AS [996]
SELECT CAST('10/25/2005 23:59:59.997' AS DATETIME) AS [997]
SELECT CAST('10/25/2005 23:59:59.998' AS DATETIME) AS [998]
SELECT CAST('10/25/2005 23:59:59.999' AS DATETIME) AS [999]
There's an issue for it in MSDN Feedback Center, which you can vote on:
http://lab.msdn.microsoft.com/produ...37-10b837e28bc3
At this point it probably won't happen until a service pack, but at
least if we all vote, maybe it will get noticed. I could have sworn
someone promised relief years ago when I first read about this problem,
but it appears that was a hallucination.
argh..
Well, back to my stupid date-trimmed unit tests. Good day,
-ChrisChris Durkin wrote:
> Guess what? The DateTime rounding bug never got fixed in SQL Server
> 2005. You know, this one:
> SELECT CAST('10/25/2005 23:59:59.990' AS DATETIME) AS [990]
> SELECT CAST('10/25/2005 23:59:59.991' AS DATETIME) AS [991]
> SELECT CAST('10/25/2005 23:59:59.992' AS DATETIME) AS [992]
> SELECT CAST('10/25/2005 23:59:59.993' AS DATETIME) AS [993]
> SELECT CAST('10/25/2005 23:59:59.994' AS DATETIME) AS [994]
> SELECT CAST('10/25/2005 23:59:59.995' AS DATETIME) AS [995]
> SELECT CAST('10/25/2005 23:59:59.996' AS DATETIME) AS [996]
> SELECT CAST('10/25/2005 23:59:59.997' AS DATETIME) AS [997]
> SELECT CAST('10/25/2005 23:59:59.998' AS DATETIME) AS [998]
> SELECT CAST('10/25/2005 23:59:59.999' AS DATETIME) AS [999]
> There's an issue for it in MSDN Feedback Center, which you can vote
> on:
>
http://lab.msdn.microsoft.com/produ...
7-10b837e28bc3
> At this point it probably won't happen until a service pack, but at
> least if we all vote, maybe it will get noticed. I could have sworn
> someone promised relief years ago when I first read about this
> problem, but it appears that was a hallucination.
>
Didn't you read the MS response on that page? It's been "Resolved as By
Design "
In other words, it's not a bug: it's the way it's supposed to work. No
matter how many votes it gets, it is not going to be "fixed".
This rounding has occurred since the SQL 6.5 days, at least. If more
resolution is needed, a different datatype needs to be used.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Not a bug. From Books Online:
"Date and time data from January 1, 1753 through December 31, 9999, 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, as shown in the table."
Exactly what enhancement are you looking for? Increasing the accuracy
from 0.00333 to 0.001 would seem to add very little value. Increasing
precision by 1 or 2 more decimals might be a more useful enhancement
but would cost extra strorage.
David Portas
SQL Server MVP
--|||<snip>

> This rounding has occurred since the SQL 6.5 days, at least. If more
> resolution is needed, a different datatype needs to be used.
What datatype are we supposed to use - is there a new datetime type in
2005 with more precision?|||I realize it's by design. You're being disingenuous. My point is that
it's bad design, which should have been fixed. A database product as
mature as SQL Server should support datetime precision in milliseconds
- at least! If it costs extra storage, who cares? GUIDs take lots of
storage, look how popular they've become. But give us the option.
The fact that SQL rounds to "one three-hundredth" of a second is
counter intuitive, and clearly shows that it is a design flaw, one that
has persisted to this day. What rational justification is there for
this behavior? Hundredths of a second, tenths, that I could see, but
every 3 milliseconds? Lame.
Maybe in the next version (SQL Server 2010) they can give us a new data
type called "bigdatetime", which allows greater precision. The old
datetime can be preserved for those purists who prefer working with
1/300 second intervals.|||Chris Durkin wrote:
> <snip>
>
> What datatype are we supposed to use - is there a new datetime type in
> 2005 with more precision?
You need to come up with your own scheme: perhaps use an int column to store
the number of milliseconds since midnight...something like that
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||>> A database product as mature as SQL Server should support datetime preci
sion in milliseconds - at least! <<
Actually, the FIPS-127 specs required at least 5 decimal places in a
TIMESTAMP; more would be better and it is getting a LOT easier to do.
I have a cheap alarm clock that sets itself to in milliseconds using
the NIST radio signal.
This is implementation is a "code museum" problem. Sybase started on
UNIX, which did a "clock tick" model of time. DB2 uses a "Cobol" model
of time which keeps separate fields for
"year-month-day-hour-minute-second-subseconds", etc.
But I agree that this is a serious flaw when you are trying to sell
your product for enterprise level and DW apps.