Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Convertsionsproblem between timestamp and bigint Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, August 6, 2009 7:28 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, December 4, 2014 7:36 AM Points: 132, Visits: 537
 Hello,I need to store the value of a timestampvalue into a different Database to determine changes at the source. To handle the datatype more easily i convert timestamp to bigint and store the value as bigint.To look for changes i Select in the source DB with where clause timestamp "greater than" convert(timestamp,"bigint-value")But here is a problem. With small timestampvalues it works fine. if these Values are quite big, the conversion of big timespamp values wont work as i need.Example:`SELECT 'THIS WORKS' , CONVERT(timestamp,1639539151) myTimeStamp , CONVERT(Bigint,CONVERT(timestamp,1639539151)) myBigint , 1639539151 OrigValueUNIONSELECT 'DON''T WORK' , CONVERT(timestamp,3639539151) , CONVERT(Bigint,CONVERT(timestamp,3639539151)) , 3639539151`Please help, how to handle the timestamp.
Post #766182
 Posted Thursday, August 6, 2009 7:10 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, June 6, 2016 1:51 PM Points: 1,400, Visits: 3,059
 This was an interesting question that got me experimenting and digging a bit into BOL. First, I found that TIMESTAMP values are stored as either BINARY(8) or VarBINARY(8) depending on whether they've been defined as NOT NULLable or NULLable. From there, I found that the breaking point for your sample code of "works" or "not works" is at the 2^31 mark. That value breaks, but (2^31) - 1 works. From there, the trail led me to this nugget in the BOL documentation of the CONVERT() function:Do not try to construct binary values and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between versions of SQL Server. This leads me to think that you'd be better off storing the values as binary(8) columns rather than the bigint datatype in your other database table. By the way, SQL 2005 BOL recommends using the synonym "rowversion" rather than "timestamp", and SQL 2008 BOL calls "timestamp" a deprecated synonym for rowversion.
Post #766640
 Posted Thursday, August 6, 2009 7:58 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, November 29, 2016 11:30 AM Points: 2,936, Visits: 1,410
 Your problem isn't with converting timestamp to bigint, it's with using bigint literals greater than 2^31. If you used the constant 0xD8EEF5CF instead of 3639539151 it might work better (I can't test it at the moment, maybe it should be 0x00000000D8EEF5CF).I wouldn't worry about using bigint for timestamp/rowversion, it's still going to be an incrementing numeric sequence and the internal structure is unlikely to change. They would have used bigint in the first place if it was available. The warning about casting numeric to binary didn't mean all numeric types including ints, it referred specifically to NUMERIC(p,s)/DECIMAL(p,s) types. Their internal structure is not defined by any outside standard (to my knowledge) and is subject to change at any time.The only problem I've had with bigint is using them in SSIS. Silly me for assuming they'd show up as Int64, the correct datatype is Binary(8). Their excuse is that they couldn't guarantee the format of bigint across all data sources (which could include big-endian hardware).
Post #766652
 Posted Friday, August 7, 2009 1:34 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, December 4, 2014 7:36 AM Points: 132, Visits: 537
 Thank you for your reply's.I tested some more with your informations. I think`SELECT 'THIS WORK' , CONVERT(timestamp,2147483647) , CONVERT(Bigint,CONVERT(timestamp,2147483647)) , 2147483647UNIONSELECT 'DON''T WORK' , CONVERT(timestamp,2147483648) , CONVERT(Bigint,CONVERT(timestamp,2147483648)) , 2147483648UNION SELECT 'Work also with gt 2^21-1 ; -)' , CONVERT(timestamp,CONVERT(bigint,2147483648)) , CONVERT(Bigint,CONVERT(timestamp,CONVERT(bigint,2147483648))) , 2147483648order by 4`should work for me.I kow this will either no work at the 2^63-1 Mark.
Post #766728
 Posted Tuesday, April 9, 2013 3:29 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, April 16, 2015 1:59 PM Points: 3, Visits: 133
 I'm also trying to figure out how to convert from a bigint value back to a timestamp, a colleague of my did it with SAS, this is the code ... proc sql noprint; select PUT(put(max_row_version, IB8.),\$HEX16.) into : maxRowVersion from ( select max(Row_version) as max_row_version from stbetlsg.tablename(firstobs=&num_m_50M) ); %let maxRowVersion=0X&maxRowVersion; %put maxRowVersion=&maxRowVersion;quit;I'm lost with this code, and he found it in the SAS. Thought somebody could figure it out.
Post #1440589
 Posted Saturday, June 27, 2015 7:14 AM
 Forum Newbie Group: General Forum Members Last Login: Saturday, June 27, 2015 7:12 AM Points: 1, Visits: 0
 I also spent hours solving this. Fortunatelly solved. It is sufficient to use this:convert(timestamp, convert(bigint, "big number"))It seems like if the SQL Server handles all the numbers as Integers
Post #1698313

 Permissions