Search code examples
sql-serversql-server-2008t-sql

SQL Server Convert Timestamp DataType to Decimal

Been Trying to figure this out and according to Cast and Convert Documentation on MSDN, this should be possible ( http://msdn.microsoft.com/en-us/library/ms187928.aspx )

Have a look at the Conversion table.

enter image description here

I am running the following Code:

CREATE TABLE TableName (
    ID bigint Identity(1,1),
    SomeValue nvarchar(20) not null,
    TimestampColumn timestamp not null)

Insert Into TableName (SomeValue)
values ('testing')

SELECT Convert(decimal, TimeStampColumn) from TableName

However I simply get the following error:

Error converting data type timestamp to numeric.

This should be possible according to the documentation or am I missing something? Note I will also need to convert back to the same timestamp value.

SELECT TimeStampColumn, Convert(timestamp, Convert(decimal, TimeStampColumn)) 
FROM TableName

In the end the above query should render the same value.

Solution

  • Try this; though MSDN says it's a implicit conversion but it actually doesn't work. So, what I am doing is converting it to INT and then to decimal (INT to decimal is implicit anyways)

    select val, CAST((CONVERT(bigint, timestampcol)) as decimal) as 'TS as decimal' 
from teststmp