Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 376
John Hartsock Avatar asked May 25 '12 17:05

John Hartsock


1 Answers

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
like image 116
Rahul Avatar answered Sep 19 '22 18:09

Rahul