How to increment DateTime column by a second for every row?

Question

Suppose that I have this Time value: 09:00:00

And I have a table with a column Time, and I have three records in it.

I want to update those 3 records with that time but with the Time value incremented in one second each time (for every record).

Something like this:

ColumnA   ColumnB
1         09:00:00
2         09:00:01
3         09:00:02

How can I do that?

My Solution:

After some time working on my own solution, this is what I came up with

update tor1
set ColumnB = dateadd(s,tor2.inc, ColumnB)
from table1 tor1
inner join (select ColumnA, ROW_NUMBER() OVER (Order by ColumnA) as inc from table1) tor2 on tor1.ColumnA=tor2.ColumnA

Solution 1

You don't specify any particular ordering.

For undeterministic/undocumented results you could try the quirky update approach.

CREATE TABLE table1
(
    ColumnB datetime NULL
);

INSERT INTO table1 DEFAULT VALUES;
INSERT INTO table1 DEFAULT VALUES;
INSERT INTO table1 DEFAULT VALUES;

DECLARE @ColumnB datetime;

SET @ColumnB = '19000101 09:00:00';

UPDATE table1
SET @ColumnB = ColumnB = DATEADD(s, 1, @ColumnB);

SELECT *
FROM table1;

DROP TABLE table1;

Otherwise you will need to use a cursor or find some way of simulating ROW_NUMBER in 2000.

Solution 2

Here's a version that uses a #temp table but doesn't use the unsupported quirky update (no offense Martin) and doesn't rely on magical identity ordering, which is not guaranteed (no offense Richard).

CREATE TABLE dbo.Whatever
(
  ColumnA INT IDENTITY(1,1), 
  ColumnB DATETIME
);

INSERT dbo.Whatever(ColumnB) SELECT '09:00';
INSERT dbo.Whatever(ColumnB) SELECT '09:00';
INSERT dbo.Whatever(ColumnB) SELECT '09:00';
INSERT dbo.Whatever(ColumnB) SELECT '09:00';
INSERT dbo.Whatever(ColumnB) SELECT '09:00';

-- just to demonstrate a gap
DELETE dbo.Whatever WHERE ColumnA = 3;

SELECT w.ColumnA, w.ColumnB, 
  c = (SELECT COUNT(*) FROM dbo.Whatever WHERE ColumnA < w.ColumnA)
INTO #x
 FROM dbo.Whatever AS w;

UPDATE w
  SET ColumnB = DATEADD(SECOND, x.c, w.ColumnB)
  FROM dbo.Whatever AS w
  INNER JOIN #x AS x
  ON x.ColumnA = w.ColumnA;

SELECT ColumnA, ColumnB FROM dbo.Whatever;

Results:

ColumnA  ColumnB
-------  -----------------------
1        1900-01-01 09:00:00.000
2        1900-01-01 09:00:01.000
4        1900-01-01 09:00:02.000
5        1900-01-01 09:00:03.000

Solution 3

If you can assume ColumnA is the number of seconds you need to add (or directly proportional like your example), then you can use that.

UPDATE myTable SET ColumnB = DATEADD(s, (ColumnA - 1), ColumnB)

If not, you will need to determine the rank of each column (plenty of results on google for that) and add the rank instead.