Inserting timestamps in SQL

[shyamal]

I am having problems inserting into a timestamp field

Field: fldtimestamp (timestamp,null)

insert into cnv_tmstmp (fldtimestamp)
values ('10/01/01:15:10:111')

or if i just put 10/01/01 I receive the following:

Disallowed implicit conversion from data type varchar to data type timestamp, table '..dbo.cnv_tmstmp', column 'fldtimestamp'. Use the CONVERT function to run this query.

How do i insert into this field?

Thanks in advance.

 

[shyamal]

By the way, inserting null results in a binary output. This is supposed to return the current timestamp what about a value?

 

[tlbroadbent]

In SQL Server the Timestamp data type is not the same as the ANSI SQL Timestamp. A SQL Timestamp column is a binary number 8 bytes in size. In reality, it is a version stamp for a record. SQL Server updates a timestamp column every time the record is updated.

I think you want to use the SQL Server DATETIME data type instead of TIMESTAMP.

SQL Books Online contains more detail about data types, including TIMESTAMP and DATETIME. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[balves]

The only time that the timestamp datatype seems to come into play is to do optimistic locking from an application written in VB, ASP, etc.

You retrieve the record from the database table, including the timestamp field (which you store in a double in VB).

Then the user fiddles with the data, and when you go to update the record back to the database table you make sure that the timestamp you retrieved in the first place is still the timestamp on the record. Otherwise, the record has been updated by someone else in the meantime and you don't update the record.

 

[sguslan]

Hi
A a SQL table can have one timestamp column which is updated automatically by SQL at insert/update time ...

It's main use is for auditing the table tranmsactional activities and aids in knowing the last activities since a given point in time ...

Use datetime instead ...

Good luck

salim