asked on

Add Seconds To Each Record in MSSQL

HELLO!

I have a table in MSSQL, where the "time" values...are all 00.00.000...for the value of the "seconds". This is all in a DATETIME field...so, for example, the data looks like the following:

2010-01-21 00:00:00.00
2010-01-21 00:00:00.00
2010-01-21 00:00:00.00
2010-01-21 00:00:00.00

...but...what I need it to be...is a "unique" time value...so I figured...OK...there should be a simple way to "add a portion of time to each record"...such that the "seconds" are altered...but the original portion of the Hours/Minutes stays intact...so that the above records would look something like:

2010-01-21 00:00:00.01
2010-01-21 00:00:00.02
2010-01-21 00:00:00.03
2010-01-21 00:00:00.04

...and so forth. I started to use the DATEADD function...but my syntax must be off...or, I'm simply thinking incorrectly, and should not, perhaps, be using the DATEADD function?

Please help...I know this has got to be something simple...Thanks!...Mark

mimran18

It should be done, something like this
Select DateAdd(ss,1,Convert(datetime,'Sep 19 2012'))
Select DateAdd(ss,2,Convert(datetime,'Sep 19 2012'))

datatechcorp

ASKER

Thanks for responding...that's what I started with...but how do you "automatically" increment the '1' or '2' portion above...is there any such syntax that can make that occur...or do I have to go with a fixed constant? I only ask...because I have over 20,000 records to update. Please let me know...and, again, thank you!...Mark

mimran18

Hi,
Instead of manual addition, you can generate an auto number and add it into the seconds.

Create Table test4(
Sdate datetime
)

Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
GO
Select ROW_NUMBER() Over (Order by Sdate),
DateAdd(ss,ROW_NUMBER() Over (Order by Sdate),Sdate) from test4

datatechcorp

ASKER

OK, but I'm still confused...a little bit.

So...remember...that my table (call it 'Tickets')...has the following data...in a FIELD called 'TicketDate':

2010-01-21 00:00:00.00
2010-01-21 00:00:00.00
2010-01-21 00:00:00.00
2010-01-21 00:00:00.00

What is the syntax, then, for the "UPDATE" statement that I need to run...to update the Tickets.TicketDate FIELD...so that those "seconds" get updated uniquely? Please let me know...and, again, thanks mimran18 for responding...it's very much appreciated!...Mark

SOLUTION

mimran18

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mimran18

Please check the output of test4 because once the number exceed from 59 it will add it in minutes and seconds as well.

mimran18

After updation, you can remove Identity column.

ralmada

If you want to update your table, just try

declare @i int

set @i = 0

update yourtable
set yourdatecolumn = dateadd(s, @i, yourdatecolumn), @i = @i + 1

Open in new window

Guy Hengel [angelIII / a3]

stepping back, I take this line :
..but...what I need it to be...is a "unique" time value
why would you need that, actually?

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

datatechcorp

ASKER

Thank you all to everyone that responded...your comments and suggestions were all very well received, and very much appreciated. To be fair, I'm going to split the points...if they'd allow me to give 500 to everyone, I definitely would! :-)

A few of you asked me about the logical reason *why* this time "thing" needed to be done in the first place. It's because Radiant/NCR has some ridiculous condition that exists in one of their Upgrade scripts...for their Point-of-Sale system...pertaining to "imported" orders...and the "time" field. Don't ask, it's complicated, and something I thought we'd *never* have to contend with...until they balked about fixing the bug(s) in their scripts; so, we had to go through this silly "data scrub" chore, as a result, before we could go forth with an Upgrade.

In a nutshell...in their Upgrade scripts...they *rejected* any/all records...which had "exact matches" of Date/Time stamps in particular fields...EVEN THOUGH this had NOTHING TO DO with the Primary Key of the table, which is actually Store/Station/Ticket/PostBatch#. Simply, ridiculous. I thought I owed you all an explanation :-)...

Thanks!...Mark