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'IsUserTable')
= 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