Strange date/time anomaly, or am I just stoopid?

To set up the problem, paste this into QA:

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

CREATE TABLE [dbo].[WorkOTRate] (
[TimeFrom] [smalldatetime] NOT NULL ,
[TimeTo] [smalldatetime] NOT NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.WorkOTRate (TimeFrom, TimeTo, RateMultiplier)
VALUES ('18:00:00', '23:59:59', 1.2)

SELECT TimeFrom, TimeTo, RateMultiplier FROM dbo.WorkOTRate

This gives the following result:

1900-01-01 18:00:00	1900-01-02 00:00:00	1.2

So, it's storing the time 23:59:59 as midnight.  That's odd.

(NOTE:  If you rescript the table using datetime instead of
smalldatetime types, the data are stored correctly.)

It gets worse (or better, if you like perversity).

If I go to Enterprise Manager, right-click on WorkOTRate and select
"Open Table" -> "Return All Rows" I get:

01/01/1900 18:00:00	02/01/1900	1.2

So, I bite the bullet and change the two column types to datetime,
clear out the old data and run the INSERT again.  The data looks better
now.

Go back to the view in EM.

If I put the cursor in a new row, and type into the TimeFrom column
18:30:00 and the TimeTo column 19:30:00 and the RateMultiplier column
1.3, and refresh the data by pressing the red shriek !, I get this:

01/01/1900 18:00:00	01/01/1900 23:59:59	1.2
18:30:00	19:30:00	1.3

If I re-run the SELECT from the QA, I get this:

1900-01-01 18:00:00.000	1900-01-01 23:59:59.000	1.2
1899-12-30 18:30:00.000	1899-12-30 19:30:00.000	1.3

Is it just me, or does this seem to be remarkably inconsistent?

Edward

teddysnips@hotmail.com  wrote:
> To set up the problem, paste this into QA:
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTa
ble')
> = 1)
> drop table [dbo].[WorkOTRate]
> GO
>
> CREATE TABLE [dbo].[WorkOTRate] (
> 	[TimeFrom] [smalldatetime] NOT NULL ,
> 	[TimeTo] [smalldatetime] NOT NULL ,
> 	[RateMultiplier] [float] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO dbo.WorkOTRate (TimeFrom, TimeTo, RateMultiplier)
> VALUES ('18:00:00', '23:59:59', 1.2)
>
> SELECT TimeFrom, TimeTo, RateMultiplier FROM dbo.WorkOTRate
>
> This gives the following result:
>
> 1900-01-01 18:00:00	1900-01-02 00:00:00	1.2
>
> So, it's storing the time 23:59:59 as midnight.  That's odd.
>
Not really. BOL says that smalldatetime stores to the nearest minute,
and the nearest minute to 23:59:59 is midnight.

> (NOTE:  If you rescript the table using datetime instead of
> smalldatetime types, the data are stored correctly.)
>
> It gets worse (or better, if you like perversity).
>
> If I go to Enterprise Manager, right-click on WorkOTRate and select
> "Open Table" -> "Return All Rows" I get:
>
> 01/01/1900 18:00:00	02/01/1900	1.2
>
> So, I bite the bullet and change the two column types to datetime,
> clear out the old data and run the INSERT again.  The data looks better
> now.
>
> Go back to the view in EM.
>
> If I put the cursor in a new row, and type into the TimeFrom column
> 18:30:00 and the TimeTo column 19:30:00 and the RateMultiplier column
> 1.3, and refresh the data by pressing the red shriek !, I get this:
>
> 01/01/1900 18:00:00	01/01/1900 23:59:59	1.2
> 18:30:00	19:30:00	1.3
>
> If I re-run the SELECT from the QA, I get this:
>
> 1900-01-01 18:00:00.000	1900-01-01 23:59:59.000	1.2
> 1899-12-30 18:30:00.000	1899-12-30 19:30:00.000	1.3
>
> Is it just me, or does this seem to be remarkably inconsistent?
>
Yes, it's inconsistent. Don't expect anything Enterprise Manager does
to ever make any kind of sense. I only use EM when there's no other
method available, or where the other method would take about 5 times
longer to accomplish it's goal. (4 times longer, do it the non-EM way
:-))

Damien

(teddysnips@hotmail.com)  writes:
> 1900-01-01 18:00:00     1900-01-02 00:00:00     1.2
>
> So, it's storing the time 23:59:59 as midnight.  That's odd.

Not particularly. As noted by Damien, smalldatetime does fit any seconds.

> If I put the cursor in a new row, and type into the TimeFrom column
> 18:30:00 and the TimeTo column 19:30:00 and the RateMultiplier column
> 1.3, and refresh the data by pressing the red shriek !, I get this:
>
> 01/01/1900 18:00:00     01/01/1900 23:59:59     1.2
> 18:30:00     19:30:00     1.3
>
> If I re-run the SELECT from the QA, I get this:
>
> 1900-01-01 18:00:00.000     1900-01-01 23:59:59.000     1.2
> 1899-12-30 18:30:00.000     1899-12-30 19:30:00.000     1.3
>
> Is it just me, or does this seem to be remarkably inconsistent?

In SQL Server time zero is 1900-01-01 00:00:00.000

In other Microsoft environments - COM, Visual Basic etc - time zero is
1899-12-30 00:00:00.000. Apparently the junior programmer that was tasked
to do the Open Table function was not aware of this difference.

Well, no one forces you to use EM.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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