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
datatechcorpAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ralmadaConnect With a Mentor Commented:
The only thing will that doesn't make sense is your comment:

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...

And then you say that there are 20,000 records. So basically you will end up adding 20,000 seconds, so you definitively will update the minutes, hours and day portions as well...
You could try adding milliseconds or nanoseconds instead, which will give you more room to update without altering the minutes and hours, but you still run the risk of doing so if you have too many records

declare @i int

set @i = 0

update yourtable
set yourdatecolumn = dateadd(ns, @i, yourdatecolumn), @i = @i + 1 --ns for nanoseconds, ms for milliseconds

Open in new window



So I guess if you want to create unique values without altering your date, you simply add a primary key/identity column to your table or use the row_number() function in your select

select row_number() over (order by yourdatecolumn) rn, yourdatecolumn from yourtable
0
 
mimran18Commented:
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'))
0
 
datatechcorpAuthor Commented:
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mimran18Commented:
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
0
 
datatechcorpAuthor Commented:
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
0
 
mimran18Connect With a Mentor Commented:
Hi,

Please try it in this way.

Drop Table test4
GO
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')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')
Insert into test4 Values ('19 sep 2012')

Alter table test4 Add [ID] int identity (1,1)

Update test4 Set [Sdate]= DateAdd(ss,[ID],Sdate)
from test4

Select * from test4
0
 
mimran18Commented:
Please check the output of test4 because once the number exceed from 59 it will add it in minutes and seconds as well.
0
 
mimran18Commented:
After updation, you can remove Identity column.
0
 
ralmadaCommented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
stepping back, I take this line :
..but...what I need it to be...is a "unique" time value
why would you need that, actually?
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Why do you need a unique time value?  Time values repeat all the time in data, and it's not a problem.

If, for example, it's used in a index, just don't explicitly define the index as unique, and SQL will handle the dup times for you, if required.

For example:

CREATE [NON]CLUSTERED INDEX <index_name> ON <table_name> ( datetime_column )

[Rather than CREATE UNIQUE ... INDEX or a unique constraint]
0
 
datatechcorpAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.