Converting TIMESTAMP to VARCHAR

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

General SQL Server Forums

Script Library

Converting TIMESTAMP to VARCHAR

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 09:58:48

[code]CREATE FUNCTION dbo.fnTS2VC
(
@ts BINARY(8)
)
RETURNS VARCHAR(16)
AS
BEGIN
RETURN SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) % 16, 1)
END[/code]Call with

SELECT dbo.fnTS2VC(@@DBTS)

You can also use SELECT CAST(@@DBTS AS BIGINT) if you only need to convert the TIMESTAMP value to INTEGER.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 07:19:08

Peter,

While searching, I came across following undocumented function in Master database which performs the same function as the script you posted.

Select master.dbo.fn_sqlvarbasetostr(@@DBTS)

which does same thing with the help of other function:

fn_varbintohexsubstring()

EDIT: This function is also available in SQL Server 2005 and still undocumented.

Harsh Athalye
India.
"Nothing is Impossible"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-27 : 07:28:02

ok i have to ask:
searching how?
master DB diving?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 07:29:37

Nope!

I was looking for some other function (fn_virtualfilestats), when I got my feet over this.

Harsh Athalye
India.
"Nothing is Impossible"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-27 : 07:31:11

cool.

i just looked in SSMS and it's nicly displayed...

i'll probably have to over the whole master db and see what it has stored...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources