Update Table with auto incrementing dates in date column

Question

hi,

I want to update a Table with auto incrementing dates in date column in Sql server 2005.

Answer 1

To label newly inserted rows, you could combine an identity column with a calculated field. For example:

declare @t table 
(
     dayNumber int identity, 
     Date as dateadd(day, dayNumber-1, '1970-01-01')
)

insert into @t default values
insert into @t default values
insert into @t default values

select * from @t

This prints:

dayNumber  Date
1          1970-01-01 00:00:00.000
2          1970-01-02 00:00:00.000
3          1970-01-03 00:00:00.000

---

To update columns in an existing table with increasing dates, use row_number, like:

declare @t2 table (id int identity, name varchar(50), DateColumn datetime)

insert @t2 (name) values ('Maggie'), ('Tom'), ('Phillip'), ('Stephen')

update  t2 
set     DateColumn = DATEADD(day, rn-1, '1970-01-01')
from    @t2 t2
join    (
        select  ROW_NUMBER() over (order by id) rn
        ,       id
        from    @t2 
        ) t2_numbered
on      t2_numbered.id = t2.id

select * from @t2

This prints:

id  name     DateColumn
1   Maggie   1970-01-01 00:00:00.000
2   Tom      1970-01-02 00:00:00.000
3   Phillip  1970-01-03 00:00:00.000
4   Stephen  1970-01-04 00:00:00.000