How to perform three date changes at once

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 2005 Forums

Transact-SQL (2005)

How to perform three date changes at once

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-09 : 10:11:43

I need to alter some dates in a table by performing three DATEADD operations on selected rows.

My table looks like this:


[ID] int IDENTITY (1,1) PRIMARY KEY,
[ExpiryDate] datetime NULL

I have three integer variables (@days, @months, @years), each representing a value I need to add to the existing date. Can anyone advise on the most efficient way to achieve this? Currently I have two options, but aren't sure whether this is the best/correct way. Can anyone advise please?

Method 1


UPDATE
	[MyTable]
SET
	expiryDate = DATEADD(DAY, @days, expiryDate),
	expiryDate = DATEADD(MONTH, @months, expiryDate),
	expiryDate = DATEADD(YEAR, @years, expiryDate)
WHERE
	ID IN (.....)

Method 2


UPDATE
	[MyTable]
SET
	expiryDate = DATEADD(DAY, @days, DATEADD(MONTH, @months, DATEADD(YEAR, @years, expiryDate)))
WHERE
	ID IN (.....)

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-09-09 : 12:16:44

Method 1 is invalid. The error is The column name 'ExpiryDate' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'ExpiryDate' may appear twice in the view definition.

Method 2 is the way to go

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-10 : 03:33:46

Yes, I thought it would be. Thanks very much MSquared

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources