SqlDateTime overflow

Question

On SQL server 2005, I have a table defined as follow:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DebugTrace](
    [Timestamp] [datetime] NOT NULL CONSTRAINT [DF_DebugTrace_Timestamp]  DEFAULT (getdate()),
    [Log] [nvarchar](max) NOT NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_DebugTrace] 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]

As you can see, the Timestamp column has a default value set to GetDate().

From the SQL Server Management Studio (running locally on the SQL server), the below code runs fine:

insert into DebugTrace ([Log])
values ('test')

The "same" code ran my workstation fails:

DebugTrace trace = new DebugTrace();
trace.Log = "test from code";
DebugTraces.InsertOnSubmit(trace);
SubmitChanges();

The exception thrown is:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Both requests do not set the Timestamp and rely on the default value to set the column value. Therefore how can the GetDate() function trigger such an exception?

P.S. to request is run from different machines but with the same Windows account

score 0 · Accepted Answer · edited May 23 '17 at 10:26

0

Does DebugTrace's property mapped to TimeStamp, have a default value? Try giving it a default value when inserting from your code

DebugTrace trace = new DebugTrace();
trace.Log = "test from code";
trace.TimeStamp= Datetime.Now;

Your problem is that when inserting, the timestamp is remaining as null. So it could be your default value constraint is not working properly. Have a look at this link, it elaborates your problem precisely.

edited May 23 '17 at 10:26

Community

1
1

answered Oct 30 '14 at 10:36

Mez

4,666
4
29
57

1

Thanks for the answer. the link you mentioned helped. Using Linqpad to perform the same action allowed to see what's actually passed to the SQL server: -- Region Parameters DECLARE p0 DateTime SET p0 = '0001-01-01 00:00:00.000' DECLARE p1 NVarChar(1000) SET p1 = 'test from code' -- EndRegion INSERT INTO [DebugTrace]([Timestamp], [Log]) VALUES (p0, p1) SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value] – E. Jaep Oct 30 '14 at 11:27

SqlDateTime overflow

1 Answers1