substring with 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 2000 Forums

Transact-SQL (2000)

substring with timestamp

Author

Topic

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-05-01 : 10:36:45

Hi,

Is it possible to retrieve the last 3 digits (miliseconds) from a column (logtimestamp) with timestamp datatype?

SELECT SUBSTRING(LOGTIMESTAMP, -1,3)
FROM mt

Thx,

Dobby

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-01 : 10:45:38

What is a LONGTIMESTAMP?

Do you mean:

SELECT SUBSTRING(CONVERT(varchar(26),Getdate(),126),21,3)

Brett

8-)

EDIT: Misread LOGTIMESTAMP..same differnce though.

Edited by - x002548 on 05/01/2003 10:46:37

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-01 : 11:22:48

quote:

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

If you're talking about datetime then

quote:

datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

This is not accurate for example

DECLARE @leftdate datetime
SET @leftdate = '2003-04-30 11:59:59.994'
SELECT LEFT(RIGHT(CONVERT(nvarchar, @leftdate,109),5),3)

---------
993

Here's what happens

quote:

Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000

01/01/98 23:59:59.995,
01/01/98 23:59:59.996,
01/01/98 23:59:59.997,
01/01/98 23:59:59.998 1998-01-01 23:59:59.997

01/01/98 23:59:59.992,
01/01/98 23:59:59.993,
01/01/98 23:59:59.994 1998-01-01 23:59:59.993

01/01/98 23:59:59.990 or
01/01/98 23:59:59.991 1998-01-01 23:59:59.990

Edited by - ValterBorges on 05/01/2003 11:23:10

Edited by - ValterBorges on 05/01/2003 11:24:07

Edited by - ValterBorges on 05/01/2003 11:25:12

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-01 : 21:09:15

Could just use:

SELECT DatePart(ms, DateVal)

If all you want are the milliseconds.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-01 : 22:21:46

nice and simple , but it's still only accurate to 3-4 milliseconds.

DECLARE @DateVal AS DATETIME
SET @DateVal = '2003-04-30 11:59:59.994'
SELECT DatePart(ms, @DateVal)

----------
993

Just one question why do you need milliseconds?

Edited by - ValterBorges on 05/01/2003 22:22:26

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources