CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
SmartestVEGA
SmartestVEGA

Reputation: 8899

Will datetime datatype in a table column update the current time automatically?

Will datetime datatype in a table column(last_modified_timestamp) update the current time automatically?

i have a table column as shown below , i need to know whether it will insert the current time in the column automatically?

How i know currently i have default settings in my table?

i changed it to insert ...not for updating !

alt text

Upvotes: 0

Views: 1373

Answers (5)

DForck42
DForck42

Reputation: 20377

sounds like you might need a trigger to update the date when an update is made.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      David Forck
-- Create date: 12Nov10
-- Description: Update last modified date
-- =============================================
CREATE TRIGGER dbo.UpdateLastModified
   ON  dbo.table1
   AFTER update
AS 
BEGIN


    SET NOCOUNT ON;

update dbo.table1
set last_modified_timestap=getdate()
where ID in (select ID from inserted)



END
GO

Upvotes: 0

freggel
freggel

Reputation: 552

If you use the use DEFAULT getdate() you must disable the nulls for that column, and if you sent a null then sql will set the default.

Upvotes: 0

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55499

Definitely NOT.

You can use DEFAULT to be getdate() which will add your current datetime for your column

If default is set on a column, you can see that in your table constraints. Check in the table properties, or type sp_help <tablename>

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

No, it will not. How would you expect SQL to guess which datetime columns should be automatically updated like this, and yet others are meant to record, e.g. historic dates.

For INSERT purposes, you can have a DEFAULT constraint on the column that inserts the current date (Getdate()/CURRENT_TIMESTAMP).

But for UPDATEs to work, you'd have to implement a trigger.

For INSERT purposes, and using the table designer, you can look at the "Default Value or Binding" property - you'd set this to (CURRENT_TIMESTAMP) or (GetDate()) (they mean the same thing). Or in the Object Explorer, you can look at the constraints on the table - if there's a default set, it will appear in there.

Also, worth pointing out that a default is exactly as it sounds - there's nothing to prevent someone providing their own value for this column. If you want to prevent this, then trigger's are probably the answer (although a lot of people dislike triggers).

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166486

No, it wont.

You either need to specify a default like

DECLARE @Table TABLE(
        ID INT,
        LastDate DATETIME DEFAULT GETDATE()
)

INSERT INTO @Table (ID) SELECT 1

SELECT  *
FROM    @Table

Or make use of triggers, or update the values manually using GETDATE() in your INSERT/UPDATES

Upvotes: 0

Related Questions