Posted by Hugo Kornelis on 02/04/06 14:32

On 3 Feb 2006 17:38:40 -0800, --CELKO-- wrote:

>>> Note the use of greater _OR EQUAL_ for start_date, but lesser (and not equal) for end_date). This will always work - both for datetime and smalldatetime, and it will
>continue to work if Microsoft ever decides to change the precision of
>their datetime datatypes. <<
>
>Good point. I keep forgetting that SQL Server does not follow the
>FIPS-127 rules about keeping at least five decimal places of seconds
>like other products. Generally goiing to 1/100 of a second has worked
>for me in the real world -- CAST ('2006-01-01 23:59:59.99' AS
>DATETIME).

Hi Joe,

This will still bite you if smalldatetime is used. Or if ever an entry
makes it into the datebase with a 23:59:99.993 timestamp.

What is your objection to
SomeDate >= StartOfInterval
AND SomeDate < EndOfInterval
(with EndOfInterval actually being equal to the first fraction of a
second after the end of the interval, or the start of the next interval
if there are consecutive intervals)

AFAICT, this will work on ALL products, regardless of the precision of
the date and time datatypes used in the product. Am I wrong?

--
Hugo Kornelis, SQL Server MVP

[Back to original message]