Copy date column into the same table as date1

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

Transact-SQL (2000)

Copy date column into the same table as date1

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-25 : 13:36:20

i have a table with 30 columns. there is a column called date column.

I need to update the dates in the table to -1 business days.

So to do this effectively I need to copy that column into another column called date1 so that when i run my queries it works fine.

As I would have to run the query to update Tueday through Friday first.

update TABLE
set date = date - 1 where
datename(weekday, new) IN ('TUESDAY', 'Wednesday', 'Thursday', 'Friday')

And then I will update for mondays and if I have another column called date1, I can do this easily and then drop that column

update testdate set new = new - 3
where datename(weekday, date) = 'Monday'
and datename(weekday, date1) = 'Monday'

Coz now orinal tuesdays are also mondays and I don't want to change them to Fridays

Ashley Rhodes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 13:40:32

[code]UPDATE t
SET date = CASE WHEN DATENAME(weekday, date) IN ('Tuesday', 'Wednesday', 'Thursday', 'Friday') THEN date - 1
WHEN DATENAME(weekday, date) IN ('Monday') THEN date - 3
END
FROM TABLE t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources