Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 add timestamp column in already existing table

Author  Topic 

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-06-26 : 08:56:43
Hi,

I would like to add a timestamp column in a table in SQL SERVER 2005. Is it possible to do it?
If yes, can you please give me a syntax.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-26 : 09:00:46
[CODE]

CREATE TABLE dbo.test (column_a INT) ;
GO

ALTER TABLE dbo.test ADD column_b Date NULL
GO
[/CODE]
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-26 : 09:02:03
quote:
Originally posted by learning_grsql

Hi,

I would like to add a timestamp column in a table in SQL SERVER 2005. Is it possible to do it?
If yes, can you please give me a syntax.

I assume you mean a column that can hold datetime values (rather than the a column of timestamp data type, which is a different thing altogether - http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx )

If so,
ALTER TABLE YourTablename ADD MyTimeStamp DATETIME NOT NULL DEFAULT GETDATE();
You can choose to have the default value, and you can choose to make it nullable.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 09:41:43
quote:
Originally posted by MuMu88

[CODE]

CREATE TABLE dbo.test (column_a INT) ;
GO

ALTER TABLE dbo.test ADD column_b Date NULL
GO
[/CODE]


Wont work in SQL 2005 as date datatype is only available from SQL 2008
so you should be using datetime instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-26 : 12:37:06
quote:
Originally posted by learning_grsql

Hi,

I would like to add a timestamp column in a table in SQL SERVER 2005. Is it possible to do it?
If yes, can you please give me a syntax.

ALTER TABLE dbo.Foo ADD Timestamp

-- or if you want a custom name for your timestamp column:
ALTER TABLE dbo.Foo ADD MyTimestamp Timestamp
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-06-28 : 08:15:30
Thank you all :)

I was looking for a column with timestamp datatype; not a column with datetime type so JamesK and Lamprey code worked for me.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-06-28 : 08:33:51
oh I confused here
Can you please tell me what the differences are between the following two codes?
A timestamp column with datetime datatype default value getdate() as given by JamesK
A timestamp column with timestamp datatype as given by Lamprey
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 10:53:31
quote:
Originally posted by learning_grsql

oh I confused here
Can you please tell me what the differences are between the following two codes?
A timestamp column with datetime datatype default value getdate() as given by JamesK
A timestamp column with timestamp datatype as given by Lamprey


timestamp datatype in sql server doesnot provide you with date or time information. Its just an column used by sql server for row version identification. Check James posted link for more info. Anyways currently its deprecated and replaced by datatype rowversion to avoid this confusion.
On the otherhand datetime,date,time,datetime2,datetimeoffset etc are the fields that supports date time values. If you want to default to current system date value you should use either of these as datatype based on your SQLServer version. for ex. date,time etc are available only from sql 2008 onwards

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-06-28 : 15:21:16
Again Thanks Visakh.
Now I got it.
That's why my values were "binary" something when I had datatype timestamp, now I deleted the column and again added new column with datatype datetime and default value getdate ().

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-29 : 14:37:44
quote:
Originally posted by learning_grsql

Again Thanks Visakh.
Now I got it.
That's why my values were "binary" something when I had datatype timestamp, now I deleted the column and again added new column with datatype datetime and default value getdate ().




Yep...Exactly
Glad that you got the idea

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -