June 27, 2003 at 10:15 am
Hello!!
I have a situation where I have a table with the following columns:
Date ID week
Currently, the week field has no values and I need to run an update statement that will
take the Date field and update the week column with the next Saturday after the Date.
Example
Date ID Week
6/10/03 4 6/14/03
6/25/03 5 6/28/03
I will probably put this in a DTS Package to update the table where Week is NULL.
Any assistance will be greatly appreciated!!
Thanks!!!!!!!!
June 27, 2003 at 10:50 am
JMeyer,
Assuming your SET DATEFIRST is set for Sunday, then this should do the trick:
DECLARE @ThisDate AS datetime,
@NextSaturday as datetime
SET @ThisDate = GetDate()
IF (7 - DatePart(dw,@ThisDate) > 0)
SET @NextSaturday = DateAdd(dd,7 - DatePart(dw,@ThisDate),@ThisDate)
ELSE
SET @NextSaturday = DateAdd(dd,7,@ThisDate)
UPDATE <tablename>
SET .
.
.
NextSaturday = @NextSaturday
.
.
.
WHERE <criteria>
SJTerrill
June 27, 2003 at 11:00 am
Might try something like this:
create table week_day([Date] datetime,
Id int identity,
[Week] datetime)
go
insert into week_day([date]) values ('6/10/03')
insert into week_day([date]) values ('6/25/03')
insert into week_day([date]) values ('6/22/03') -- sunday
insert into week_day([date]) values ('6/23/03') -- monday
insert into week_day([date]) values ('6/24/03') -- tuesday
insert into week_day([date]) values ('6/25/03') -- wednesday
insert into week_day([date]) values ('6/26/03') -- thursday
insert into week_day([date]) values ('6/27/03') -- friday
insert into week_day([date]) values ('6/28/03') -- saturday
select * from week_day
-- did this to make Sunday The first day of he week.
set datefirst 7
update week_day
set week=DATEADD(DAY,CASE WHEN (7-DATEPART(WEEKDAY,[DATE])) = 0
THEN 7
ELSE (7-DATEPART(WEEKDAY,[DATE]))
end,[date])
select * from week_day
drop table week_day
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
June 27, 2003 at 11:25 am
Worked Flawlessly!!
Thank you so much for your help- I greatly appreciate it!!!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply