Weird date rounding

[Edward]

SQL Server 7.0

The following SQL:

SELECT TOP 100 PERCENT fldTSRID, fldDateEntered
FROM tblTSRs WITH (NOLOCK)
WHERE ((fldDateEntered >= CONVERT(DATETIME, '2003-11-21 00:00:00',
102))
AND
(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:59', 102)))

returns this record:

fldTSRID: 4
fldDateEntered: 24/11/2003

Hello? How is 24/11/2003 <= '2003-11-23 23:59:59'?

I tried decrementing the second predicate by seconds:

(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:30', 102)))

returns the record, but

(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:29', 102)))

does NOT.

What is happening here?

Edward

============================

TABLE DEFINITION:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRNotes_tblTSRs]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSRNotes] DROP CONSTRAINT FK_tblTSRNotes_tblTSRs
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSRs]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblTSRs]
GO

CREATE TABLE [dbo].[tblTSRs] (
[fldTSRID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDealerID] [int] NOT NULL ,
[fldWorkshopGroupID] [int] NULL ,
[fldSubjectID] [int] NULL ,
[fldReasonID] [int] NULL ,
[fldFaultID] [int] NULL ,
[fldContactID] [int] NULL ,
[fldMileage] [int] NULL ,
[fldFirstFailure] [smalldatetime] NULL ,
[fldNumberOfFailures] [int] NULL ,
[fldTSRPriorityID] [int] NULL ,
[fldTSRStatusID] [int] NULL ,
[fldAttachedFilePath] [char] (255) NULL ,
[fldFileAttached] [smallint] NOT NULL ,
[fldFaultDescription] [ntext] NULL ,
[fldFaultRectification] [ntext] NULL ,
[fldEmergency] [int] NOT NULL ,
[fldDateEntered] [smalldatetime] NOT NULL ,
[fldEnteredBy] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

[David Portas]

DATETIME is only precise to 3 milliseconds, so '2003-11-23 23:59:59' rounds
to '2003-11-24 00:00:00'. Use < instead of <= with dates to avoid this sort
of problem:

SELECT fldtsrid, flddateentered
FROM tblTSRs
WHERE fldDateEntered >= '20031121'
AND fldDateEntered < '20031124'

Stick to the ISO year-month-day formats and you don't need the extra CONVERT
function in the query.

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

Correction: Although my solution should be work, my explanation wasn't
accurate. It's true that the precision of DATETIME is limited to 3
milliseconds but that doesn't appear to be the root of your problem since
you haven't used milliseconds.

The precision of SMALLDATETIME is 1 minute but the value of '23:59:59' in
your query shouldn't get rounded up to the following midnight. Instead the
SMALLDATETIME value from your table should be implicitly cast to DATETIME
with no loss of precision. Your query is therefore sub-optimal since the
implicit conversion will be performed for every row. However, the query
should not produce the result you described.

I can't actually reproduce your problem. Try the query I posted previously.
If that doesn't help, please post some code to reproduce the problem,
including INSERT statement(s) with sample data.

--
David Portas
------------
Please reply only to the newsgroup
--

[Simon Hayes]

te********@hotmail.com (Edward) wrote in message news:<25**************************@posting.google. com>...

SQL Server 7.0

The following SQL:

SELECT TOP 100 PERCENT fldTSRID, fldDateEntered
FROM tblTSRs WITH (NOLOCK)
WHERE ((fldDateEntered >= CONVERT(DATETIME, '2003-11-21 00:00:00',
102))
AND
(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:59', 102)))

returns this record:

fldTSRID: 4
fldDateEntered: 24/11/2003

Hello? How is 24/11/2003 <= '2003-11-23 23:59:59'?

I tried decrementing the second predicate by seconds:

(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:30', 102)))

returns the record, but

(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:29', 102)))

does NOT.

What is happening here?

Edward

============================

TABLE DEFINITION:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRNotes_tblTSRs]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSRNotes] DROP CONSTRAINT FK_tblTSRNotes_tblTSRs
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSRs]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblTSRs]
GO

CREATE TABLE [dbo].[tblTSRs] (
[fldTSRID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDealerID] [int] NOT NULL ,
[fldWorkshopGroupID] [int] NULL ,
[fldSubjectID] [int] NULL ,
[fldReasonID] [int] NULL ,
[fldFaultID] [int] NULL ,
[fldContactID] [int] NULL ,
[fldMileage] [int] NULL ,
[fldFirstFailure] [smalldatetime] NULL ,
[fldNumberOfFailures] [int] NULL ,
[fldTSRPriorityID] [int] NULL ,
[fldTSRStatusID] [int] NULL ,
[fldAttachedFilePath] [char] (255) NULL ,
[fldFileAttached] [smallint] NOT NULL ,
[fldFaultDescription] [ntext] NULL ,
[fldFaultRectification] [ntext] NULL ,
[fldEmergency] [int] NOT NULL ,
[fldDateEntered] [smalldatetime] NOT NULL ,
[fldEnteredBy] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The smalldatetime data type is accurate to one minute - according to
BOL, a smalldatetime value is rounded down if the seconds are 29 or
less, or up or 30 or more seconds.

Although I don't have a SQL7 installation to test this, I seem to
remember that for comparisons, the column type took precedence over
the expression. So your datetime is converted to smalldatetime and
then compared. In SQL 2000, the reverse would happen, because data
type precedence is strictly defined, and datetime is higher than
smalldatetime.

Simon

[Erland Sommarskog]

David Portas (RE****************************@acm.org) writes:

Correction: Although my solution should be work, my explanation wasn't
accurate. It's true that the precision of DATETIME is limited to 3
milliseconds but that doesn't appear to be the root of your problem since
you haven't used milliseconds.

The precision of SMALLDATETIME is 1 minute but the value of '23:59:59' in
your query shouldn't get rounded up to the following midnight. Instead the
SMALLDATETIME value from your table should be implicitly cast to DATETIME
with no loss of precision. Your query is therefore sub-optimal since the
implicit conversion will be performed for every row. However, the query
should not produce the result you described.

But the rules for implicit conversion were changed from SQL7 to SQL2000,
and Edward runs SQL7.

I am not going to try to explain the conversion rules for SQL7, as this
was the version that I more or less skipped. However, testing I find
that:

create table #temp (a smalldatetime)
insert #temp values ('20031123')
insert #temp values ('20031124')
go
select * from #temp where a <=
CONVERT(DATETIME, '2003-11-23 23:59:59', 102)

return two rows on SQL7 on SQL 6.5 and one row on SQL2000.

David's suggestion to use:

SELECT fldtsrid, flddateentered
FROM tblTSRs
WHERE fldDateEntered >= '20031121'
AND fldDateEntered < '20031124'
is of course the bulletproof way of running the query.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

> But the rules for implicit conversion were changed from SQL7 to SQL2000,

and Edward runs SQL7.

Ah, the light dawns! Sorry, I missed that vital piece of info from Edward's
post.

--
David Portas
------------
Please reply only to the newsgroup
--

[louis nguyen]

Hi All,

Why the "102"? I thought that was only needed to return the date in a
character format/style. Just curious.

CONVERT(DATETIME, '2003-11-23 23:59:59', 102)

[Erland Sommarskog]

louis nguyen (lo************@hotmail.com) writes:

Why the "102"? I thought that was only needed to return the date in a
character format/style. Just curious.

CONVERT(DATETIME, '2003-11-23 23:59:59', 102)

The 102 forces interpretation of the datetime literal according to that
format.

Using YYYYMMDD HH:MM:SS is easier - then you don't need to force any
format, and you can skip the convert.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Edward]

te********@hotmail.com (Edward) wrote in message news:<25**************************@posting.google. com>...

<Snippage>

Thanks to you all for your great help, as usual.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk