How to convert SQL Server’s timestamp column to datetime format

by

SQL Server’s TIMESTAMP datatype has nothing to do with a date and time!

It’s just a hexadecimal representation of a consecutive 8 byte integer – it’s only good for making sure a row hasn’t change since it’s been read.

You can read off the hexadecimal integer or if you want a BIGINT. As an example:

SELECT CAST (0x0000000017E30D64 AS BIGINT)

The result is 

400756068

In newer versions of SQL Server, it’s being called RowVersion – since that’s really what it is. See the MSDN docs on ROWVERSION:

Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism
for version-stamping table rows. The
rowversion data type is just an incrementing number and does not
preserve a date or a time. To record a date or time, use a datetime2
data type.

So you cannot convert a SQL Server TIMESTAMP to a date/time – it’s just not a date/time.

But if you’re saying timestamp but really you mean a DATETIME column – then you can use any of those valid date formats described in the CAST and CONVERT topic in the MSDN help. Those are defined and supported “out of the box” by SQL Server. Anything else is not supported, e.g. you have to do a lot of manual casting and concatenating (not recommended).

The format you’re looking for looks a bit like the ODBC canonical (style = 121):

DECLARE @today DATETIME = SYSDATETIME()

SELECT CONVERT(VARCHAR(50), @today, 121)

gives:

2011-11-14 10:29:00.470

SQL Server 2012 will finally have a FORMAT function to do custom formatting……

More Related Contents:

  1. Sql Server string to date conversion
  2. How can I truncate a datetime in SQL Server?
  3. What is the significance of 1/1/1753 in SQL Server?
  4. How to get Time from DateTime format in SQL?
  5. How to get first and last day of previous month (with timestamp) in SQL Server
  6. How to remove the time portion of a datetime value (SQL Server)?
  7. Need a datetime column in SQL Server that automatically updates when the record is modified
  8. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  9. Date conversion and culture: Difference between DATE and DATETIME
  10. SQL Query to find the last day of the month
  11. Handling date in SQL Server
  12. How can I get the sum of multiple datetime values?
  13. Comparing dates stored as varchar
  14. Add default value of datetime field in SQL Server to a timestamp
  15. SQL Server 2008 Row Insert and Update timestamps
  16. Best way to store time (hh:mm) in a database
  17. Retrieving the date in SQL Server; CURRENT_TIMESTAMP vs GetDate()
  18. SQL Server Group by Count of DateTime Per Hour?
  19. Why SQL Server DATETIME type saves time in ticks of 1/300 of a sec?
  20. SQL Server: convert ((int)year,(int)month,(int)day) to Datetime [duplicate]
  21. Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime
  22. Convert UTC Milliseconds to DATETIME in SQL server
  23. How does SQL Server decide format for implicit datetime conversion?
  24. Undesired rounding of DateTime in SQL Server
  25. How to convert text column to datetime in SQL
  26. How to calculate difference in hours (decimal) between two dates in SQL Server?
  27. Import Multiple CSV Files to SQL Server from a Folder
  28. Regular Expressions in SQL Server servers?
  29. Unique constraint on multiple columns
  30. Diagnosing Deadlocks in SQL Server 2005

Leave a Comment