How do I add time to a datetime field with an indexing value?

How do I add time to a datetim开发者_StackOverflowe field with an indexing value?

My RDBMS is SQL Server 2008 R2.

I want to start at noon for a datetime field and for every record, take the base value of noon and add 15 seconds and set it for the record.

Example

Record 1: DateTime Value = '12:00:00 pm'
Record 2: DateTime Value = '12:00:15 pm'
Record 3: DateTime Value = '12:00:30 pm'
...n...

I toyed with a UPDATE query, but I couldn't get it to index. I'm feeling this might require a function, but I'm not sure.

Ideas?

Respectfully,

Ray

Assuming that you have a table YourTable with a datetime column Value that you need to fill with a datetime value incremented by 15 seconds for each row. Here I use a column ID to specify the order of the rows.

declare @Start datetime = '2011-05-18T12:00:00'

;with cte as
(
  select Value,
    row_number() over(order by ID) as rn
  from YourTable 
)
update cte set
  Value = dateadd(s, 15*(rn-1), @Start)

With a recursive CTE (Common Table Expression) you could do this pretty easily:

;WITH DateTimes AS
(
    SELECT 
        CAST('2011-05-18T12:00:00pm' AS DATETIME) AS YourTime,
        1 AS RowNum

    UNION ALL

    SELECT
        DATEADD(SECOND, 15, dt.YourTime),
        dt.RowNum + 1
    FROM
        DateTimes dt
    WHERE   
        dt.RowNum < 50
)
SELECT *
FROM DateTimes

Mind you: you need to make sure yourself to stop the recursion before the default max recursion depth of 100 has been reached (that's what I use the RowNum column for) - otherwise, SQL Server will tell you loud and clear that it doesn't like this recursive CTE :-)

This produces an output of:

YourTime                RowNum
2011-05-18 12:00:00.000 1
2011-05-18 12:00:15.000 2
2011-05-18 12:00:30.000 3
2011-05-18 12:00:45.000 4
....
....
2011-05-18 12:12:00.000 49
2011-05-18 12:12:15.000 50

So if you have a DATETIME value that has no time like this:

DECLARE @Today DATETIME 
SET @Today = CAST(GETDATE() AS DATE)

SELECT @Today   -- gives: 2011-05-18 00:00:00.000

you could easily add time values to it from your recursive CTE (you could even adapt it to return only TIME and add that to your DATETIME column):

SELECT  
    CAST(YourTime AS TIME),
    @Today + CAST(YourTime AS TIME) AS 'NewValue'
FROM TimeValues

Look at the DATEADD function and this may help you.

http://msdn.microsoft.com/en-us/library/ms186819.aspx