如何在 MSSQL 中将 VARCHAR 转换为 TIMESTAMP? [英] How do you convert VARCHAR to TIMESTAMP in MSSQL?

问题描述

您想在 T-SQL 中调用参数类型为 TIMESTAMP 的 MS SQL 上的存储过程，而不是使用 VARCHAR 值(例如0x0000000002C490C8")的 ADO.NET.

You'd like to call a stored proc on MS SQL that has a parameter type of TIMESTAMP within T-SQL, not ADO.NET using a VARCHAR value (e.g. '0x0000000002C490C8').

你在做什么?

更新:在这里，您有一个时间戳"值，但仅作为 VARCHAR 存在.(想想另一个存储过程上的 OUTPUT 变量，但它已经固定为 VARCHAR，它只有 TIMESTAMP 的值).因此，除非您决定构建动态 SQL，否则如何以编程方式将存储在 VARCHAR 中的值更改为有效的 TIMESTAMP?

UPDATE: This is where you have a "Timestamp" value coming at you but exists only as VARCHAR. (Think OUTPUT variable on another stored proc, but it's fixed already as VARCHAR, it just has the value of a TIMESTAMP). So, unless you decide to build Dynamic SQL, how can you programmatically change a value stored in VARCHAR into a valid TIMESTAMP?

推荐答案

时间戳数据类型由 SQL Server 管理.除了作为表列类型之外，我从未见过它在任何地方使用.在这种情况下，时间戳类型的列将为您提供与数据库中所有其他更新相关的行上最后一次插入/更新的严格顺序.要查看整个数据库中最新的序号，您可以检索@@DBTS 或 rowversion() 的值.

A timestamp datatype is managed by SQL Server. I've never seen it used anywhere other than as a table column type. In that capacity, the column of type timestamp will give you a rigorous ordinal of the last insert/update on the row in relation to all other updates in the database. To see the most recent ordinal across the entire database, you can retrieve the value of @@DBTS or rowversion().

每个 http://msdn.microsoft.com/en-us/library/ms182776(SQL.90).aspx

时间戳 (Transact-SQL)

是一种数据类型，它在数据库中公开自动生成的唯一二进制数.时间戳通常用作版本标记表行的机制.存储大小为 8 个字节.时间戳数据类型只是一个递增的数字，不保留日期或时间.要记录日期或时间，请使用日期时间数据类型.

timestamp (Transact-SQL)

is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.

因此，时间戳列的 volatile 值无法设置，并且在对该行进行任何修改时可能会发生变化.但是，您可以将时间戳值冻结为 varbinary(8) 值.

Hence, the volatile value of a timestamp column cannot be set and is subject to change upon any modifaction to the row. You can, however, freeze the timestamp value to a varbinary(8) value.

例如，假设您有一个源表和一个目标表.

For example, say you had a source table and a target table.

CREATE TABLE tblSource (
Id int not null
colData int not null
colTimestamp timestamp null)

CREATE TABLE tblTarget (
Id int not null
colData int not null
colTimestampVarBinary varbinary(8) null)

然后，在提取过程中，您可能希望捕获自上次运行提取过程以来已更新的所有内容.

Then, in an extraction process, you might want to capture everything that has been updated since the last time you ran the extraction process.

DECLARE @maxFrozenTargetTimestamp varchar(8)
SELECT @maxFrozenTargetTimestamp = max(colStamp) FROM tblTarget

INSERT tblTarget(Id, colData, colTimestampVarBinary)
SELECT 
Id
,colData
colTimestampVarBinary = convert(varbinary(8) colTimestamp)
FROM 
tblSource 
WHERE
tblSource.colTimestamp > @maxFrozenTargetTimestamp

如果您遇到问题，我的第一个猜测就是您的问题的症结所在是将 varchar 转换为 varbinary(8)，而不是时间戳类型.

If you are having issues, my first guess would be that crux of your problem is in the conversion of a varchar to a varbinary(8), and not to a timestamp type.

有关更多信息(可能太多)，请参阅我留给博客文章的评论(第四个)http://vadivel.blogspot.com/2004/10/about-timestamp-datatype-of-sql-server.html?showComment=1213612020000

For more info (perhaps too much) , see the comment (fourth one down) I left to the blog post http://vadivel.blogspot.com/2004/10/about-timestamp-datatype-of-sql-server.html?showComment=1213612020000

这篇关于如何在 MSSQL 中将 VARCHAR 转换为 TIMESTAMP?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！