Learn how to a build a cloud-first strategyRegister Now


Sql Server Convert Timestamp to Readable date/time format

Posted on 2004-11-21
Medium Priority
Last Modified: 2012-06-21
is there any built-in function or udf to convert Timestamp to Readable date/time format.

pls help me with this.

tnx in advance!
Question by:doc_jer
  • 3
  • 2
LVL 11

Expert Comment

ID: 12641825
TimeStamp means ..are you referring to T-SQL 'TimeStamp" datatype? (or) a datetime datatype field?

T-SQL TimeStamp is not a datetime field so you cannot convert it to datetime.

If it is a valid datatime you can use "Convert" function to change it to the required format. See SQL books online for Convert Syntax.

Author Comment

ID: 12641864
yah im referring to timestamp datatype. so, how cn i convert that value into readable date format not a datetime datatype.

like this "0x000000000000083D" what is the value in date format?

Accepted Solution

ctcampbell earned 400 total points
ID: 12641895
A timestamp is not a datetime data type and cannot be converted into a readable date.  In essence, it is simply a counter to know that timestamp A occurred before timestamp B.  Here is a bit from the Books Online:

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.

A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type."

If you want a readable date/time value, use datetime, CONVERT, and GETDATE().
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 11

Expert Comment

ID: 12641898
This is not a date at all.

This is description for BOL for the TimeStamp:

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.

So, This field is used only to generate a unique value to each row in database. NOT as a date field.
LVL 11

Expert Comment

ID: 12641903
Ah..ctcampbell, same time..same posting..surprising !!

Expert Comment

ID: 12641958
Truly amazing...  exact same time :)

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question