Column based on another column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Column based on another column

Author

Topic

mase2hot
Starting Member

36 Posts

Posted - 2010-04-24 : 10:40:03

Hi,

I have a column which contains a date, I want another column in the table to be +10 days of the original column.

How can I do this? I tried default value with no luck...

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 11:06:56

[code]update table
set othercol = datecol+ 10[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mase2hot
Starting Member

36 Posts

Posted - 2010-04-24 : 11:26:08

thanks for that, I presume this is a one off thing? Would I need to make it into a trigger for it to happen ongoing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 11:28:13

why do you need a trigger for that? why not make it computed then?

the table will be created as


CREATE TABLE tablename
( 
other fields ..,
datecol datetime,
othercol AS datecol + 10
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mase2hot
Starting Member

36 Posts

Posted - 2010-04-24 : 11:46:41

how do I alter an existing table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 12:00:10

ALTER TABLE ADD othercol AS datecol + 10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mase2hot
Starting Member

36 Posts

Posted - 2010-04-24 : 12:29:17

Done but get error:

Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-24 : 13:18:05

quote:
Originally posted by mase2hot

Done but get error:

Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

Looks like DateCol is of Date datatype.

You need to use DateAdd function...

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 13:22:09

ALTER TABLE ADD othercol AS DATEADD(dd,10,datecol)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mase2hot
Starting Member

36 Posts

Posted - 2010-04-24 : 15:02:28

great that worked, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-25 : 02:19:32

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources