Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add one MilliSecond to each row Expand / Collapse
Author
Message
Posted Friday, July 26, 2013 10:06 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:35 PM
Points: 975, Visits: 3,350
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
Post #1478086
Posted Friday, July 26, 2013 10:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 3,541, Visits: 7,637
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1478090
Posted Friday, July 26, 2013 10:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 1,081, Visits: 6,514
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.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1478100
Posted Friday, July 26, 2013 10:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 12,910, Visits: 32,014
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1478101
Posted Friday, July 26, 2013 10:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 3,541, Visits: 7,637
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1478103
Posted Friday, July 26, 2013 10:31 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:35 PM
Points: 975, Visits: 3,350
Thank You Everyone

I will try the code sample

Have a great weekend.

Andrew SQLDBA
Post #1478105
Posted Sunday, July 28, 2013 6:59 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1478422
Posted Saturday, August 3, 2013 6:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 36,984, Visits: 31,509
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1480749
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse