CODEWITHSUNDEEP
sqlsql-server
Ctrl_Alt_Defeat
Ctrl_Alt_Defeat

Reputation: 4009

SQL Server Rolling date over

When I try to update my end date column using the below:

update MyTable set EndDate = '2013-07-31 23:59:59.999' where Id = 40

The result I am seeing on my table is rolling over to the next day so my column is actually showing as: 

2013-08-01 00:00:00.000

If I take off the milliseconds it leaves it as it but any reason why this is rolling over?

Upvotes: 0

Views: 260

Answers (2)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Datetime column's precision is not 1 millisecond, but actually 10/3 ms.

Valid entries always end with 0,3 or 7

So 23:59:59.997 is last possible value in any given day. When you tried to insert .999 it rounded up to the nearest valid value, which was midnight - next day.

DATETIME type info

Upvotes: 3

jacouh
jacouh

Reputation: 8769

THis is very interesting finding. I tested this table tblTest in SQL Sever 2008, with Birthday as datetime2 and DateEntry as datetime that is the same as your EndDate column, datetime2 may be better than datetime to fit your precision:

CREATE TABLE [dbo].[tblTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NULL,
    [Birthday] [datetime2](7) NULL,
    [DateEntry] [datetime] NULL,
 CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,             ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO tblTest (Name) VALUES('Spielberg');

UPDATE tblTest SET DateEntry = '2013-07-31 23:59:59.999' WHERE ID=1

Shows exactly the same result as yours:

2013-08-01 00:00:00.000

Now Birthday as datetime2, the precision is satisfying:

UPDATE tblTest SET Birthday = '2013-07-31 23:59:59.999' WHERE ID=1

SELECT result is: 2013-07-31 23:59:59.9990000

Upvotes: 0

Related Questions