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

teddysnips@xxxxxxxxxxx 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'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.
>
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

.