SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Doubt in displaying TimeStamp
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ganeshkumar08
Posting Yak Master

India
187 Posts

Posted - 05/25/2009 :  04:54:42  Show Profile  Send ganeshkumar08 an AOL message  Reply with Quote
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  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 05/25/2009 :  05:01:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
1. Create a view as "SELECT GETDATE()"
2. SELECT * FROM YourTablenameHere CROSS JOIN YourViewNameHere



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 05/25/2009 :  05:01:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT *, DATEADD(MILLISECOND, ID, GETDATE()) FROM YourTableNameHere



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

ganeshkumar08
Posting Yak Master

India
187 Posts

Posted - 05/25/2009 :  05:26:52  Show Profile  Send ganeshkumar08 an AOL message  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/25/2009 :  05:33:29  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 05/25/2009 :  05:35:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

ganeshkumar08
Posting Yak Master

India
187 Posts

Posted - 05/25/2009 :  05:38:34  Show Profile  Send ganeshkumar08 an AOL message  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/25/2009 :  05:50:52  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 05/25/2009 :  05:55:11  Show Profile  Reply with Quote
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

Go to Top of Page

ganeshkumar08
Posting Yak Master

India
187 Posts

Posted - 05/25/2009 :  06:11:36  Show Profile  Send ganeshkumar08 an AOL message  Reply with Quote
One of my client is asking to do like that.

Solutions are easy. Understanding the problem, now, that's the hard part
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 05/25/2009 :  06:22:22  Show Profile  Reply with Quote
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

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/25/2009 :  06:22:35  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 05/25/2009 :  06:24:48  Show Profile  Reply with Quote
And how much the clients is paying you for this . . .


KH
Time is always against us

Go to Top of Page

ganeshkumar08
Posting Yak Master

India
187 Posts

Posted - 05/25/2009 :  06:43:57  Show Profile  Send ganeshkumar08 an AOL message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1 seconds. Powered By: Snitz Forums 2000