My "data default (getdate())", it doesn't work

Question

I'm trying to insert in my SQL Server table the current date.

My table includes 3 files:

CREATE TABLE [dbo].[MyTable] 
(
     [Id]   INT IDENTITY (1, 1) NOT NULL,
     [Name] NVARCHAR (50) NOT NULL,
     [Date] DATE 
         CONSTRAINT [DF_MyTable_Date] DEFAULT (getdate()) NOT NULL,

     CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([Id] ASC)
) 

When a new user wants to register in the system, he has only to insert his name.

In my table, the Id is generated automatically and the date too, but the date shows 01/01/0001 instead of the current day.

Where is the mistake?

Answer 1

if you create a datetime variable in C# like

var today_date = new DateTime();

and do a Console.WriteLine(today_date); u can see it print 0001-01-01

So this is default value of null..

Use DBNull.Value to insert a SQL NULL from C# and check the result

Answer 2

(Your_Date_Column) Make it Null / Not Null and give default value GetDate() but still it will not work. You have to create a trigger like this,

CREATE TRIGGER [dbo].[Trigger_Date] ON [dbo].[TableName] FOR INSERT AS BEGIN

Declare @Id int
set @Id = (select Id from inserted)

Update [dbo].[TableName]
Set Your_Date_Column = GetDate()
Where Id = @Id

END

Answer 3

Functions and triggers are not required. Just set a column with type Date or DateTime to NOT NULL DEFAULT CURRENT_TIMESTAMP.

Updated example code from the question:

CREATE TABLE [dbo].[MyTable] 
(
     [Id]   INT IDENTITY (1, 1) NOT NULL,
     [Name] NVARCHAR (50) NOT NULL,
     [Date] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

     CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([Id] ASC)
)