Geek Answers Handbook

Refresh table with automatic incremental dates in date column

I want to update a table with index auto incrementing date in date in Sql server 2005.

+4
source share
1 answer

To mark newly inserted rows, you can combine the identity column with the computed field. For instance:

 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

Fingerprints:

 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 , for example:

 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

Fingerprints:

 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
+4
source

More articles:


All Articles