TimeStamp Data type to record transaction time

Question

TimeStamp Data type to record transaction time

pmwagne

Old Hand

Points: 306
More actions
April 11, 2012 at 10:08 pm

#259044

SQL Server Timestamp Data type

Viewing 8 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I believe the DATETIME datatype corresponds to TIMESTAMP in Access. You can then use one of the Date/Time functions to create a DEFAULT CONSTRAINT on the column.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

pmwagne Old Hand Points: 306 More actions · Answer 2

Thanks Jack. Since the table already exists, is it possible to use an ALTER TABLE Script to alter the column? Or would you suggest dropping the column and Inserting the column? This is my script thusfar,

ALTER TABLE CustomerT

ADD EditDate datetime

CONSTRAINT getdate()

GO

MSG 102 Syntax near '('

Thanks again,

Paul

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

If the column already exists and is one of the date data types then you should be able to ALTER it to add the constraint.

Your T-SQL would be:

ALTER TABLE x

ALTER COLUMN y DATETIME CONSTRAINT DF_date DEFAULT getdate();

If you are adding a new column it would be:

ALTER TABLE x

ADD y DATETIME NOT NULL CONSTRAINT DF_date DEFAULT getdate();

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

pmwagne Old Hand Points: 306 More actions · Answer 4

Thanks Jack! It worked! Kind of. I have realized this function is for timestamping a record when it is 'appended' to a table so it worked perfectly just as I had asked lol. Any ideas on a function that changes when current or existing records in the table are changed? I was thinking SYSDATETIME or Current_Timestamp hmm....

Thanks again! Progress is good,

Paul

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

If you want to update the column whenever it is edited you have 2 options with SQL Server.

1. Include the column in the update statement either using GETDATE() or passing in a direct value from the UI.

2. Create a trigger that sets the value using GETDATE(). Here's an article I wrote on triggers, http://www.sqlservercentral.com/articles/Triggers/64214/

SYSDATETIME and CURRENT_TIMESTAMP work in exactly the same away as GETDATE(). CURRENT_TIMESTAMP is included in SQL Server because it is part of ANSI standard SQL. SYSDATETIME was added along with the DATETIME2 datatype an dreturns a DATETIME2 datatype instead of DATETIME which is what GETDATE() and CURRENT_TIMESTAMP return.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

pmwagne Old Hand Points: 306 More actions · Answer 6

Thanks for your timeliness. I will review this fantastic data and get back with you soon.

Thanks for your time,

Paul

pmwagne Old Hand Points: 306 More actions · Answer 7

Hi Jack,

This is what I have thusfar as my Trigger. Do I need to add an INSERT INTO statement after Setting the Trig variable? Thanks!

USE Database

GO

CREATE TRIGGER AddDate

ON CustomerT

AFTER UPDATE

AS

Declare @Trig datetime

Set @Trig = GetDate()