Doubt in displaying TimeStamp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Doubt in displaying TimeStamp

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2009-05-25 : 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

12543 Posts

Posted - 2009-05-25 : 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

30421 Posts

Posted - 2009-05-25 : 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

30421 Posts

Posted - 2009-05-25 : 05:01:42

SELECT *, DATEADD(MILLISECOND, ID, GETDATE()) FROM YourTableNameHere

E 12°55'05.63"
N 56°04'39.26"

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2009-05-25 : 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

12543 Posts

Posted - 2009-05-25 : 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

30421 Posts

Posted - 2009-05-25 : 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

187 Posts

Posted - 2009-05-25 : 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

12543 Posts

Posted - 2009-05-25 : 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)

17689 Posts

Posted - 2009-05-25 : 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
[spoiler]Time is always against us[/spoiler]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2009-05-25 : 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)

17689 Posts

Posted - 2009-05-25 : 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
[spoiler]Time is always against us[/spoiler]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 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)

17689 Posts

Posted - 2009-05-25 : 06:24:48

And how much the clients is paying you for this . . .

KH
[spoiler]Time is always against us[/spoiler]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2009-05-25 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources