SQL column with default date and time

The following shows how to specify default datetime field for a field in MSSQL and MYSQL. You do not have to specify it in the query. It is automatically filled when a record is inserted.

MSSQL

In MSSQL the function used to get default date and time is GetDate(), there for this must be used when specifying the default value.

create table test_tbl (
id int not null primary key identity,
test_name varchar(25),
datecreated smalldatetime default  GETDATE()
)

Now insert a value

insert into test_tbl (fname) values (‘test user’)

This will populate the value for datecreated as = 2011-11-07 08:53:00, which is the current date and time.

MYSQL

In my sql, the function used to get current datetime is NOW(). Hence the following query will be used to accomplish the same

create table test_tbl (
id int not null primary key identity,
test_name varchar(25),
datecreated smalldatetime default  NOW()
)

Then insert a value

insert into test_tbl (fname) values (‘test user 2’)

*Note that MYSQL query has not  been test*

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s