| Author |
 Topic  |
|
|
ganeshkumar08
Posting Yak Master
India
187 Posts |
 Posted - 05/25/2009 : 04:54:42
|
Hi,
My table displays many rows.
While displaying each row i have to show SELECT time value for each row. Each rows must show some unique in seconds or Milli seconds etc.
For eample
1 Ganesh 2009-05-25 14:22:17.347
2 kumar 2009-05-25 14:22:36.153
How can i achieve this.
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/25/2009 : 04:59:37
|
Can you give more info about what you want.
sounds like you want a random unique datetime on each row.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 05/25/2009 : 05:01:02
|
1. Create a view as "SELECT GETDATE()"
2. SELECT * FROM YourTablenameHere CROSS JOIN YourViewNameHere
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 05/25/2009 : 05:01:42
|
SELECT *, DATEADD(MILLISECOND, ID, GETDATE()) FROM YourTableNameHere
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
ganeshkumar08
Posting Yak Master
India
187 Posts |
Posted - 05/25/2009 : 05:26:52
|
I need exact retrieving time of SQL engine.
Each is retrieved by sql engine from Memory, i need that exact time to show.
Solutions are easy. Understanding the problem, now, that's the hard part |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/25/2009 : 05:33:29
|
How are you retrieving the rows?
Presumably one at a time if you want a different time on each.
A datetime is only accurate to about 3 milliseconds so you might not be able to use that datatype.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 05/25/2009 : 05:35:16
|
quote:
Originally posted by ganeshkumar08
I need exact retrieving time of SQL engine.
See answer posted 05/25/2009 : 05:01:02
And as nr wrote, the accuracy is only to a 1/300th of a second.
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
ganeshkumar08
Posting Yak Master
India
187 Posts |
Posted - 05/25/2009 : 05:38:34
|
This is my select statement.
Select dbo.fn_ResolveMatchID(matchid) as 'Match',
dbo.fn_ResolvePlayerID(PlayerID) as 'Player',
SegmentID ,
dbo.fn_ResolveEventID(EventID) As 'Event' ,
Outcome ,Time ,
XPosOrigin ,YPosOrigin ,XPosDest ,YPosDest ,
--- I need extra column which showing Time stamp for each row
TimeStampvale
from TrendEvents
Union ALL
Select dbo.fn_ResolveMatchID(matchid) as 'Match',
dbo.fn_ResolvePlayerID(PlayerID) as 'Player',
SegmentID ,
dbo.fn_ResolveEventID(EventID) As 'Event' ,
Outcome ,Time ,
XPosOrigin ,YPosOrigin ,XPosDest ,YPosDest,
--- I need extra column which showing Time stamp for each row
TimeStampvale
from TrendEvents
Have a look into query
Solutions are easy. Understanding the problem, now, that's the hard part |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/25/2009 : 05:50:52
|
Problem is that there will be a difference between the retrieval of the data from the tables, the return from the functions and the delivery in the resultset.
I suspect you might want the value returned from one of the functions (not so easy).
Also wit all those functions I suspect this might be quite slow to run.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 05/25/2009 : 05:55:11
|
ganeshkumar08,
Maybe you can explain why do you need a unique time stamp on your result ?
What's the purpose of that ?
Or you just need a unique number across the result ?
KH
Time is always against us
|
|
|
|
ganeshkumar08
Posting Yak Master
India
187 Posts |
Posted - 05/25/2009 : 06:11:36
|
One of my client is asking to do like that.
Solutions are easy. Understanding the problem, now, that's the hard part |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 05/25/2009 : 06:22:22
|
Can you do it in your frontend where you are displaying the data ?
Use row_number() to generate a running sequence number and use that to generate your unique timestamp
The query below demonstrate how to generate the seq_no. And also a time_stamp generate from the seq_no based on the current time. It will be much better and ensure uniqueness if you can do this in your front end.
select *,
seq_no = row_number() over(order by somecol),
time_stamp = dateadd(millisecond, (row_number() over(order by somecol)) * 4, dateadd(minute, datediff(minute, 0, getdate()), 0))
from yourtable
KH
Time is always against us
|
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/25/2009 : 06:22:35
|
Think you should ask the client why he wants it and what he expects it to represent.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 05/25/2009 : 06:24:48
|
And how much the clients is paying you for this . . . 
KH
Time is always against us
|
|
|
|
ganeshkumar08
Posting Yak Master
India
187 Posts |
Posted - 05/25/2009 : 06:43:57
|
client is asking to do some sort operations. What is he says we have to do, Thanks the problem
Solutions are easy. Understanding the problem, now, that's the hard part |
|
|
| |
Topic |
|
Doubt in displaying TimeStamp
