sql server - TSQL DEFAULT CURRENT_TIMESTAMP -

i have following table

create table t3 (   dd datetime default current_timestamp ) 

when start insert rows t3 via this:

insert t3 select 1; 

i

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 values in year 1900 ?

i using sql server express 2012 . clock on machine current .

your insert t3 without explicit column list expect source of values insert contains value every column in table except identity/rowversion or computed columns.

your table has single such column , select statement source supplies 1 column. result inserting explicit integer value 1 column dd.

that column's datatype datetime requiring implicit cast. result of select cast(1 datetime) 1900-01-02 hence results see.

the legacy datetime datatypes allow implicit casts int , float , treat numeric values days since 1900-01-01. newer datatypes such datetime2 not support these implicit cast , raise error instead.

default constraint values applied when don't insert explicit value column.

in case can either

insert t3 default values; 

or

insert t3 (dd) values (default)