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?

You must be sending it a `new DateTime()`. Make your date variable nullable (`DateTime?`) and make sure its value is null. — Crowcoder, May 16 '15 at 14:36
If I put a Null in the DATE variable, when I click in "View Data" I get a NULL instead of the current date. — , May 16 '15 at 14:50
I don't know what "View Data" is or how it is implemented. I created your table in my sql server and issued this query and successfully got today's date. The problem is in your .net code which you have not shown. `insert into MyTable (name) values ('foobar');` — Crowcoder, May 16 '15 at 14:57
With "View Data" I was talking about the SQL Server Object Explorer inside my Visual Studio. I checked my sql server and it works with the current date like you said, but I don't understand why that date doesn't shows in the SQL Server Object Explorer inside my Visual Studio, when I to register an user in my asp.net mvc web application. — , May 16 '15 at 15:39
Your default only ever gets used if you **do not** specify that column in your insert, e.g. if you have `INSERT INTO dbo.MyTable(Name) VALUES(N'xxxxxx');` - if you specify the column and pass in a value (or even `NULL`), then your default **will NOT be used**. — marc_s, May 16 '15 at 16:10
The problem is in your C# code, not your SQL. Edit your question and show the C# code you use to insert. — Scott Chamberlain, May 16 '15 at 17:04
The above 3 comments are your root problem and solution. (said slightly 3 different ways.) Follow Crowcoder Jorge and marc_s advice. — Sql Surfer, May 16 '15 at 22:23

score 0 · Answer 1 · answered May 16 '15 at 16:45

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

score 0 · Answer 2 · answered Aug 09 '18 at 21:11

(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

score 0 · Answer 3 · answered Jul 14 '21 at 17:17

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)
)

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

3 Answers3