How do I get date/time information from a TIMESTAMP column?

by

TIMESTAMP is an unfortunate name the SQL Server team gave the data type. It is for concurrency, and has nothing to do with date or time – they’ve recommended using its alias, ROWVERSION to prevent confusion. From this Books Online article, “In DDL statements, use rowversion instead of timestamp wherever possible.”

Unfortunately you won’t be able to derive any date/time details from the ROWVERSION column you already have, but if this information is important, you should add CreatedDate / ModifiedDate columns, for example:

ALTER TABLE dbo.foo ADD CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE dbo.foo ADD ModifiedDate DATETIME NULL;

Then create a TRIGGER that fires on UPDATE to keep the ModifiedDate value current. You may need to decide whether you want the ModifiedDate to be NULL or equal to CreatedDate on initialization.

More Related Contents:

  1. If condition in SQL Server 2008
  2. Comma separated results in SQL
  3. Understanding PIVOT function in T-SQL
  4. Which are more performant, CTE or temporary tables?
  5. Using RegEx in SQL Server
  6. Infinite loop CTE with OPTION (maxrecursion 0)
  7. How Can I Log and Find the Most Expensive Queries?
  8. Import Excel spreadsheet columns into SQL Server database
  9. SqlServer: Login failed for user
  10. SQL Server 2008 Empty String vs. Space
  11. How do I use the CONCAT function in SQL Server 2008 R2?
  12. How can I automate the “generate scripts” task in SQL Server Management Studio 2008?
  13. Is it possible to force row level locking in SQL Server?
  14. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  15. How to add xml encoding to xml Output in SQL Server
  16. How to: Create trigger for auto update modified date with SQL Server 2008
  17. How to update primary key
  18. error when inserting into table having instead of trigger from entity data framework
  19. Case sensitive variables in SQL Server
  20. What happens to the primary key Id when it goes over the limit?
  21. SQL Recursive CTE: Finding objects linked by property
  22. Convert decimal time to hours and minutes
  23. Sort Bullets in Database
  24. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  25. How to find the boundaries of groups of contiguous sequential numbers?
  26. How to wait for 2 seconds?
  27. Find broken objects in SQL Server
  28. Error converting data types when importing from Excel to SQL Server 2008
  29. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?
  30. Populate Dataset With Table Names From Stored Procedure

Leave a Comment