TSQL DEFAULT CURRENT_TIMESTAMP

Question

I have the following table

CREATE TABLE T3
(
  DD DATETIME DEFAULT CURRENT_TIMESTAMP
)

When start to insert rows to T3 via this:

INSERT T3 SELECT 1;

I get

1900-01-02 00:00:00.000
1900-01-02 00:00:00.000
1900-01-02 00:00:00.000
1900-01-02 00:00:00.000

Why are the values in year 1900 ?

I am using sql server express 2012 . The clock on my machine is current .

What did you expect the behaviour of that statement was going to be? Trying to understand the thinking behind the `SELECT 1` in the first place. — Martin Smith, Jul 10 '13 at 21:05
erm what do you think default current_timestamp is meant to do? — Tony Hopkinson, Jul 10 '13 at 21:20
why did I get -2 ? Did I do something against stackoverflow guidelines ? — dan_l, Aug 20 '13 at 19:14

Martin Smith · Answer 1 · 2013-07-10T21:28:23.783

Your INSERT T3 without an explicit column list will expect a source of values for the insert that contains a value for every column in the table except IDENTITY/ROWVERSION or computed columns.

Your table has a single such column and the SELECT statement source supplies one column. So the result is that you are inserting an explicit integer value 1 to column DD.

That column's datatype is datetime requiring an implicit cast. The result of SELECT CAST(1 AS DATETIME) is 1900-01-02 hence the results you see.

The legacy datetime datatypes allow implicit casts from int and float and treat the numeric values as days since 1900-01-01. The newer datatypes such as datetime2 do not support these implicit cast and would raise an error instead.

Default constraint values are only applied when you don't insert an explicit value for that column.

In your case you can do this either by

INSERT T3 DEFAULT VALUES;

Or

INSERT T3 (DD) VALUES (DEFAULT)

And "Day 0" in SQL Server is 1900-01-01 - thus inserting "1" results in 1900-01-02 — marc_s, Jul 10 '13 at 20:59

TSQL DEFAULT CURRENT_TIMESTAMP

1 Answers1