Timestamp

Question

Post reply

Timestamp

Best-330874

SSCrazy

Points: 2350
More actions
April 18, 2007 at 7:26 am

#118185

Hello, I would like to add a timestamp column to my table.Can Somebody tell me how i can do it?

USE [Audit]
GO
/****** Object: Table [dbo].[Audit_Project_Header] Script Date: 04/18/2007 09:21:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Audit_Project_Header](
[MSBA_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LEA_Code] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[District_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[School_Code] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[School_Name] [varchar](55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Architect] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[General_Contractor] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Project_Manager] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Audit_Firm_ID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MSBA_Audit_Firm_Name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Project_Start_Date] [datetime] NULL,
[Project_End_Date] [datetime] NULL,
[Project_Eligibility_Cutoff_Date] [datetime] NULL,
[Grant_Rate] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Audit_Project_Header] PRIMARY KEY CLUSTERED
(
[MSBA_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Do you know that a timestamp column is NOT A DATE equivalent in any way?

CREATE TABLE #x (a int not null primary key clustered)

Insert into #x (a) values (1)

SELECT * FROM #x

alter table #x

add ColName TIMESTAMP NOT NULL

Insert into #x (a) values (2)

SELECT * FROM #x

DROP TABLE #x

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 2

If you're wanting to record the date/time that the record was created, you don't want to use the Timestamp datatype as is unrelated to datetimes.

Instead, add this field to the table:

[Created_Date] [datetime] NOT NULL DEFAULT GETDATE()

This will record the current datetime for the row's creation unless you specify a different value.

Terri-92562 SSCommitted Points: 1614 More actions · Answer 3

Be aware that the Timestamp data type has been deprecated. In SQL 2000 and 2005 it has been aliased by ROWVERSION, a more descriptive name. I would recommend adding a ROWVERSION column instead of the TIMESTAMP column if you want to track whether a row has changed. If you want to know when a row was created use a DATETIME column.

Terri

To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]