Converting Timestamp

[PaulD]

Hi,

Is there anyway to convert the SQL Timestamp field into a date and time
field?

Thanks,

Paul

[Stu]

Timestamp actually has nothing to do with time (in SQL Server anyway);
from the Books Online:

timestamp
timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database. timestamp
is used typically as a mechanism for version-stamping table rows. The
storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp
data type defined in the SQL-92 standard. The SQL-92 timestamp data
type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server™ may modify the behavior
of the Transact-SQL timestamp data type to align it with the behavior
defined in the standard. At that time, the current timestamp data type
will be replaced with a rowversion data type.

Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the
timestamp data type. Use rowversion instead of timestamp wherever
possible in DDL statements. rowversion is subject to the behaviors of
data type synonyms. For more information, see Data Type Synonyms.

In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a
column name for the timestamp data type:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp)

If you do not supply a column name, SQL Server generates a column name
of timestamp. The rowversion data type synonym does not follow this
behavior. You must supply a column name when you specify rowversion.

A table can have only one timestamp column. The value in the timestamp
column is updated every time a row containing a timestamp column is
inserted or updated. This property makes a timestamp column a poor
candidate for keys, especially primary keys. Any update made to the row
changes the timestamp value, thereby changing the key value. If the
column is in a primary key, the old key value is no longer valid, and
foreign keys referencing the old value are no longer valid. If the
table is referenced in a dynamic cursor, all updates change the
position of the rows in the cursor. If the column is in an index key,
all updates to the data row also generate updates of the index.

A nonnullable timestamp column is semantically equivalent to a
binary(8) column. A nullable timestamp column is semantically
equivalent to a varbinary(8) column.

You'll want to use the datetime datatype for a column. You can have
that column default to CURRENT_TIMESTAMP or GETDATE(), if you want to
insert the current time when you add a row.

HTH,
Stu