July 10, 2006 at 1:10 pm
Hello everyone,
I need to store a timestamp value from a record table A, in another table B.
Later I want to select all the records from table A that have a timestamp value grater than the one stored in B.
What data type should I use to store this timestamp value?
July 10, 2006 at 4:10 pm
Hello..
I would suggest a datetime value, set to the current UTC time (GETUTCDATE()).
Hope this helps!
- Ward Pond
blogs.technet.com/wardpond
July 11, 2006 at 1:16 am
I was refering to a SQL server timestamp field - the one that has nothing to do with date or time! It has a binary value
July 11, 2006 at 1:28 am
In this instance, then, I would use SQL Server's timestamp datatype.
I'm a litle confused about your "binary value" statement; timestamps are far from binary. So I may still not be giving you the best advice.
Hope this helps!
- Ward Pond
blogs.technet.com/wardpond
July 11, 2006 at 6:24 am
Timestamps as we know them from other DBs are equivalent to SQL datetime datatype. SQL Server timestamps are something else,
I quote from BOL:
"The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamp values are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. Each time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the timestamp values had no relationship to time. "
July 11, 2006 at 8:15 am
I found it !
A closer look in BOL revealed:
"A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column"
April 18, 2012 at 12:13 pm
I know this was 6 years ago, but I am having the same problem today. Did anyone ever come up with an answer? I have tried saving to a Binary(8) column and converting it to Binary(8), and no luck updating the table where I am tring to save it.
UPDATE [TS].[dbo].[MPPS Data Vault]
SET [LastBarcodeTimeStamp] = (SELECT CONVERT(varbinary(max), MAX(timestamp), 1) as TS From [TS].[dbo].[Barcodes])
WHERE RowCounter = 1
Any help?
April 18, 2012 at 12:48 pm
Simple, don't use timestamp as a datatype. It is deprecated. http://msdn.microsoft.com/en-us/library/ms182776%28v=sql.105%29.aspx
If you want to record dates for things like LastUpdated use datetime.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 12:48 pm
charles-1011021 (4/18/2012)
I know this was 6 years ago, but I am having the same problem today. Did anyone ever come up with an answer? I have tried saving to a Binary(8) column and converting it to Binary(8), and no luck updating the table where I am tring to save it.UPDATE [TS].[dbo].[MPPS Data Vault]
SET [LastBarcodeTimeStamp] = (SELECT CONVERT(varbinary(max), MAX(timestamp), 1) as TS From [TS].[dbo].[Barcodes])
WHERE RowCounter = 1
Any help?
What are the data types for these fields?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2012 at 12:52 pm
Looks like you started a couple new threads for this as well.
Further replies, please post here:
http://qa.sqlservercentral.com/Forums/Topic1285914-391-1.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply