SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 add timestamp column in already existing table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 06/26/2013 :  08:56:43  Show Profile  Reply with Quote
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

547 Posts

Posted - 06/26/2013 :  09:00:46  Show Profile  Reply with Quote


CREATE TABLE dbo.test (column_a INT) ;
GO

ALTER TABLE dbo.test ADD column_b Date NULL 
GO
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 06/26/2013 :  09:02:03  Show Profile  Reply with Quote
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.


Edited by - James K on 06/26/2013 09:02:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/26/2013 :  09:41:43  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88



CREATE TABLE dbo.test (column_a INT) ;
GO

ALTER TABLE dbo.test ADD column_b Date NULL 
GO



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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/26/2013 :  12:37:06  Show Profile  Reply with Quote
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

Edited by - Lamprey on 06/26/2013 12:38:23
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 06/28/2013 :  08:15:30  Show Profile  Reply with Quote
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 - 06/28/2013 :  08:33:51  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/28/2013 :  10:53:31  Show Profile  Reply with Quote
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 - 06/28/2013 :  15:21:16  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/29/2013 :  14:37:44  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000