Update loop to set date field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Update loop to set date field?

Author

Topic

boardtc
Starting Member

7 Posts

Posted - 2004-12-01 : 10:22:58

For each row returned in the above query I want to update LastUpdated to a unique date starrting with GetDate() - 2 weeks and then adding +1 for each date to be set.

-- returns n rows
update conditions
select LastUpdated from conditions
where LastUpdated is NULL

Instead of +1 I could add thr "row number" :
select RowNumber = (select count(*)
from conditions as a2
where a2.oid <= a1.oid),
LastUpdated,
from conditions as a1

How can I write a loop to do this?

Thnaks, Tom.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-01 : 10:28:36

no need for loop:


declare @t table (id int identity(1,1), col1 datetime)
insert into @t (col1)
select null union all
select null union all
select null union all
select null union all
select null union all
select null union all
select null 

select * from @t

declare @date datetime
set @date = dateadd(wk, -2, getdate())

update @t
set @date = col1 = dateadd(d,1,@date)

select * from @t

Go with the flow & have fun! Else fight the flow

boardtc
Starting Member

7 Posts

Posted - 2004-12-01 : 10:38:49

Cool!
Trying to get my head around this. The code as is shows each date as the same (sql server 2k) and not different by a ms (each datetime is 2004-11-18 14:58:20.233).
Assuming the laster trying to fugure how I could use it for my existing conditions table.

thanks, tom.

boardtc
Starting Member

7 Posts

Posted - 2004-12-01 : 10:49:25

so i need to do something like...

update conditions
set LastUpdated = dateadd(wk, -2, getdate()) + m.rank
where (LastUpdated is NULL)

-- m.rank us row number *below returns all rows...
and
(
SELECT
( SELECT COUNT(*) FROM conditions T WHERE t.oid <= M.oid ) AS Rank
FROM conditions M
--where T.oid = conditions.oid)
)

can't figure it...

boardtc
Starting Member

7 Posts

Posted - 2004-12-01 : 11:05:32

Understanding you code more now...trying....

declare @date datetime
set @date = dateadd(wk, -2, getdate())

update conditions
set LastUpdated = @date + dateadd(d,1,@date)
where (LastUpdated is NULL)
but LastUpdate set to same value...

tom.

boardtc
Starting Member

7 Posts

Posted - 2004-12-01 : 11:11:06

This sets all the nulls to the same date :

declare @date datetime
set @date = dateadd(week, -2, getdate())

update conditions
set LastUpdated = dateadd(millisecond,1,@date)
where (LastUpdated is NULL)

I can't see how I can do it without some loop. I need all the numm dates to be different by 1 millisecond.

Thanks, Tom.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-01 : 11:24:10

by one milisecond? emmm maybe you should read about datetime in BOL.

from BOL:
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

Go with the flow & have fun! Else fight the flow

boardtc
Starting Member

7 Posts

Posted - 2004-12-01 : 11:28:25

Ok. But that's not the point i think...Even if I do
set LastUpdated = dateadd(millisecond,1000,@date)
is sets them to all be the same, a 1 second or 1 minute difference would be fine too, rather than 1 millisecond.

thanks, Tom.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-01 : 11:31:32

you forgot something:
update conditions
set @date = LastUpdated = dateadd(s, 1, @date)
where (LastUpdated is NULL)

Go with the flow & have fun! Else fight the flow

boardtc
Starting Member

7 Posts

Posted - 2004-12-01 : 11:35:34

dude!!!!!
i can't pretend to understand how that works!
somehow the @date = LastUpdated works recursively i think...
thanks for your patience,
superb, thanks, tom.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-01 : 11:38:02

read this:
http://www.sqlteam.com/item.asp?ItemID=765

same thing can be applied to dates as you can see

...

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources