Add one MilliSecond to each row

Question

Post reply

Add one MilliSecond to each row

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
July 26, 2013 at 10:06 am

#296010

Hello Everyone
I am trying something, that I am not sure is possible.
I have a CreateDate column in a table, there are approx 5000 rows with the same date and time. This was done from the data load. I need to change the the time by adding one millisecond to each one, but incrementing by one from the previous row.
So if I have a datetime like this:
RowID, CreateDate
1, 2013-07-30 08:20:40:010
2, 2013-07-30 08:20:40:010
3, 2013-07-30 08:20:40:010
4, 2013-07-30 08:20:40:010
5, 2013-07-30 08:20:40:010
I would like to add one millisecond to each, but incrementing from the previous row.
RowID, CreateDate
1, 2013-07-30 08:20:40:010
2, 2013-07-30 08:20:40:011
3, 2013-07-30 08:20:40:012
4, 2013-07-30 08:20:40:013
5, 2013-07-30 08:20:40:014
Here is some dummy code.
; WITH DifferentTimes
(
RowID
, CreateDate
)
AS
(
SELECT TOP (500) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])), '2013-07-30 08:20:40:010'
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
)
SELECT * FROM DifferentTimes
So after that, I am not sure how I can perform an update to increment the time by each row, using the previous row.
Thank You in advance for all your assistance, suggestions and comments
Andrew SQLDBA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

First of all, you can't add a milisecond to a datetime, you need to use datetime2.

Here's an example, if you remove the CAST, you'll see what I mean.

SELECT TOP (500) DATEADD( ms, ROW_NUMBER() OVER (ORDER BY s1.[object_id]), CAST('2013-07-30 08:20:40:010' AS datetime2))

FROM sys.all_objects AS s1

CROSS JOIN sys.all_objects AS s2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

AndrewSQLDBA (7/26/2013)
Hello Everyone
I am trying something, that I am not sure is possible.
I have a CreateDate column in a table, there are approx 5000 rows with the same date and time. This was done from the data load. I need to change the the time by adding one millisecond to each one, but incrementing by one from the previous row.
So if I have a datetime like this:
RowID, CreateDate
1, 2013-07-30 08:20:40:010
2, 2013-07-30 08:20:40:010
3, 2013-07-30 08:20:40:010
4, 2013-07-30 08:20:40:010
5, 2013-07-30 08:20:40:010
I would like to add one millisecond to each, but incrementing from the previous row.
...

Think of it in a different way - add RowID-1 milliseconds to your datetime2 value.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Lowell SSC Guru Points: 323504 More actions · Answer 3

As An FYI for datetime, Luis mentioned you can't add a single millisecond; the minimum is 3 milliseconds, and that's imposed due to the way the data is stored by SQL server behind the scenes as two separate 4 byte integers.

http://msdn.microsoft.com/en-us/library/aa258277(v=sql.80).aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Thank you for the reference Lowell, I knew I had read it, but I wasn't sure where to start looking for it again.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

Thank You Everyone

I will try the code sample

Have a great weekend.

Andrew SQLDBA

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

May I ask why you need to add 1 millisecond to each row?

In other words, what is the problem you're facing when all of the bulk insert CreatedDates are the same?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

dwain.c (7/28/2013)
May I ask why you need to add 1 millisecond to each row?
In other words, what is the problem you're facing when all of the bulk insert CreatedDates are the same?

This is why I sometimes don't answer a question until they give me an answer. Sometimes folks just forget to post back because they're busy. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)