question

jacobmat9 avatar image
jacobmat9 asked

​I want to create a table with currentdate as DateCreated column in it. So when the data comes in everyday, it should show the current date for that item.. (want to start new Inventory table)

I want to create a table with currentdate as DateCreated column in it. So when the data comes in everyday, it should show the current date for that item.. (want to start new Inventory table)

calculated column
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So, is your question "How do I create a table that has a non-nullable column with a default of the current date and time?" Another thing to clarify, is whether your operation will span more than one time zone.

0 Likes 0 ·

Yes, The non-nullable column must be a current date (only date, no time needed). Only one time zone.

0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered

This is a very simple question, probably a part of homework. A visit to the default definitions section of the create table page would clearly point you in the right direction. Usually, in order to get the answers for homework related questions it is best to show some effort, some work which has been attempted that did not lead to a solution. Nevertheless, this question has been baking here for a long time so here is the answer:

When creating table, define your DateCreated column as date, not datetime since you don't need the time part of the day to be included anyway. Assigning a default value either as current_timestamp or getdate() would do the trick then. Please note that you can just define a default while creating table or optionally define a named constraint. The only difference is that in first case the constraint will be created with some random name. Here is the part of the script, what you are looking for is the line beginning with DateCreated:

set ansi_nulls on;
set quoted_identifier on;
go
create table dbo.Inventory (
    -- this is just an example, actual column names need to be used here
    InventoryID int not null identity(1, 1) primary key clustered,
    ItemID int not null,
    -- some other columns' definitions...
    DateCreated date not null default (current_timestamp),
    -- etc
);
go

You may use getdate() in place of current_timestamp, either one will work. Also, you could explicitly provide constraint name, in which case the line adding DateCreated column should read something like this:

DateCreated date not null constraint DF_Inventory_DateCreated default (current_timestamp),

Hope this helps.

Oleg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.