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) ;GOALTER TABLE dbo.test ADD column_b Date NULL GO[/CODE] |
|
|
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. |
|
|
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) ;GOALTER TABLE dbo.test ADD column_b Date NULL GO[/CODE]
Wont work in SQL 2005 as date datatype is only available from SQL 2008so you should be using datetime instead------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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. |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-06-28 : 08:33:51
|
oh I confused hereCan 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 JamesKA timestamp column with timestamp datatype as given by Lamprey |
|
|
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 hereCan 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 JamesKA 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 (). |
|
|
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...ExactlyGlad that you got the idea ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|