Timestamp default value [Programming SQL]

Prev: year week table help
Next: rebuild reorg proc

From: Stephen on 16 Apr 2010 14:07

Unlike other databases (eg. PostgreSQL and ANSI SQL), TIMESTAMP in SQL Server is not the same as DATETIME. The value contained is changed on every update and is used for row versioning/optimistic concurrency.

DATETIME is what you need. In the future, Microsoft may rename TIMESTAMP type to ROWVERSION.

Erland Sommarskog wrote:

Re: Timestamp default value
15-Feb-07

RBC (RBC(a)discussions.microsoft.com) writes:

There is no such data type in SQL Server. You would have to use two
columns.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Previous Posts In This Thread:

On Wednesday, February 14, 2007 2:50 PM
RB wrote:

Timestamp default value
Dear to Whom This Concern,

I have a table with a timestamp field and when I import data from a Excel
sheet, the field display <Binary data>;

I like to fill in default value for Timestamp. Usually I use CONSTRAINT
[TableName_FieldName] DEFAULT (N'Default_Value');

I have tried following statements (No success):

[FieldName] timestamp NOT NULL
CONSTRAINT [CONSTRAINT_FieldName] DEFAULT (current_timestamp),
[FieldName] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
[FieldName] timestamp DEFAULT now() NOT NULL,
[FieldName] TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
[FieldName] timestamp DEFAULT current_timestamp,
[FieldName] timestamp NOT NULL default CURRENT_TIMESTAMP on update,

Any suggestion?

Thank you,
Rune

On Wednesday, February 14, 2007 3:20 PM
AlejandroMes wrote:

RBC,create table dbo.
RBC,

create table dbo.t1 (c1 datetime not null default (getdate()), c2 timestamp
not null)
go

insert into dbo.t1 default values
go

select * from dbo.t1
go

update dbo.t1
set c1 = dateadd(minute, 1, c1)
go

select * from dbo.t1
go

drop table dbo.t1
go

AMB

"RBC" wrote:

On Wednesday, February 14, 2007 3:26 PM
raibeart wrote:

Timestamp is not a date and time.
Timestamp is not a date and time.

If you want a date/time column, use datetime and set the default to
getdate()

On Wednesday, February 14, 2007 3:30 PM
AlejandroMes wrote:

Seems I deleted the explanation when I pasted the code.
Seems I deleted the explanation when I pasted the code.

Timestamp data type is a binary number used as a mechanism for
version-stamping table rows. Use datetime data type instead.

AMB

"Alejandro Mesa" wrote:

On Wednesday, February 14, 2007 3:50 PM
RB wrote:

I like Timestamp better then Row Version, because timestamp display date and
I like Timestamp better then Row Version, because timestamp display date and
time, plus a number series (I can see and understand timestamp).
I assume I get only date and time with your code?
How do I increment the number series in Timestamp?

Thank you,
Rune

"Alejandro Mesa" wrote:

On Wednesday, February 14, 2007 4:06 PM
Aaron Bertrand [SQL Server MVP] wrote:

I think you are confused between DATETIME and TIMESTAMP.
I think you are confused between DATETIME and TIMESTAMP.

TIMESTAMP and ROWVERSION are the same. They do not contain any information
about date or time at all.

Number series? Do you want date/time or a number?

A

On Wednesday, February 14, 2007 10:45 PM
Erland Sommarskog wrote:

RE: Timestamp default value
RBC (RBC(a)discussions.microsoft.com) writes:

No, it does not. timestamp is just an 8-byte number, unique over the
database.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

On Wednesday, February 14, 2007 10:52 PM
RB wrote:

Hi,I like to have date,time and a number.
Hi,
I like to have date,time and a number.
Thank you,
Rune

"Aaron Bertrand [SQL Server MVP]" wrote:

On Thursday, February 15, 2007 11:00 PM
Erland Sommarskog wrote:

Re: Timestamp default value
RBC (RBC(a)discussions.microsoft.com) writes:

There is no such data type in SQL Server. You would have to use two
columns.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Submitted via EggHeadCafe - Software Developer Portal of Choice
Break the Roles in SharePoint Lists
http://www.eggheadcafe.com/tutorials/aspnet/c3ac8915-3861-4406-bec7-42f2d9110d79/break-the-roles-in-sharep.aspx

From: Stephen on 16 Apr 2010 14:09

|
Pages: 1
Prev: year week table help
Next: rebuild reorg proc