CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
fc123
fc123

Reputation: 918

sql current date constraint

I need to add a constraint to one table in my database. The table name is Experience. And there is a column named ToDate. Every time the select statement executes like following.

select ToDate from Experience

It should return current date.

So every time select statement executes, the ToDate column get updated with current date.

I know I can do this with some type of sql trigger but is there a way to do it by sql constraint.

like

alter table add constraint...

Any help will be appreciated.

Thanks

Upvotes: 1

Views: 7544

Answers (4)

Jake
Jake

Reputation: 328

You cannot use a constraint, because a constraint is basically a rule on what can go in the table, how the table can relate to others, etc. It has no bearing on the data in the table once it goes into the table. Now if I am understanding you correctly, you want to update the ToDate column whenever you select that column. Now you can't use a trigger either as mentioned here and here. They suggest a stored procedure where you would use an update followed by an insert. This is probably my preferred SQL method to go with if you have to use it repeated, which you seem to have to do. Though Andomar's answer is probably better.

Upvotes: 1

Tak
Tak

Reputation: 1562

To add contraint ...

create table tbl (id int identity, dt datetime, colval varchar(10))


ALTER TABLE dbo.tbl
ADD CONSTRAINT col_dt_def
DEFAULT GETDATE() FOR dt;

Example of inserting to the table ..

insert into dbo.tbl(colval)
select 'somevalue'

select * from dbo.tbl

The result will be ..

id  dt  colval
1   2014-08-19 13:31:57.577 somevalue

Upvotes: 1

senthilkumar2185
senthilkumar2185

Reputation: 2568

Try this link code make help full

http://www.sqlatoms.com/queries/how-to-use-the-getdate-function-in-sql-server-3/

CREATE TABLE ProductOrders
(
  OrderId int NOT NULL PRIMARY KEY IDENTITY,
  ProductName nvarchar(50) NOT NULL,
  OrderDate datetime NOT NULL DEFAULT GETDATE()
)

Upvotes: 0

Andomar
Andomar

Reputation: 238126

You can use a computed column. That's specified like colname as <expression>:

create table t1(id int, dt as getdate());
insert t1 values (1);
select * from t1;

Upvotes: 6

Related Questions