Automaticaly popluating Current Date in a Db Field

Hi,
I have a field in he db called LogDate. I have made it a timestamp type. Is
there a formula or a default value i can set for this field in the table
design view so that everytime a record is added, this field is automaticalll
y
populated with teh current date?
Thanks
--
pmudDid youy read anything in the SQL Server documentation about the timestamp
datatype? In spite of its poor name, it has nothing to do with date or
time.
Try the following instead:
CREATE TABLE dbo.MyStuff
(
StuffName NVARCHAR(32) PRIMARY KEY,
CreatedDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
GO
INSERT dbo.MyStuff(StuffName) SELECT N'foo';
WAITFOR DELAY '00:00:01';
INSERT dbo.MyStuff(StuffName) SELECT N'bar';
GO
SELECT StuffName, CreatedDate FROM dbo.MyStuff;
DROP TABLE dbo.MyStuff;
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
> Hi,
> I have a field in he db called LogDate. I have made it a timestamp type.
> Is
> there a formula or a default value i can set for this field in the table
> design view so that everytime a record is added, this field is
> automaticallly
> populated with teh current date?
> Thanks
> --
> pmud|||First, you need to define the field as a datetime data type. The timestamp
data type is used for concurrency checking using optomistic locking and does
not represent an actual date and time.
I typically use a default value of CURRENT_TIMESTAMP to put the current date
and time in a field.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
> Hi,
> I have a field in he db called LogDate. I have made it a timestamp type.
> Is
> there a formula or a default value i can set for this field in the table
> design view so that everytime a record is added, this field is
> automaticallly
> populated with teh current date?
> Thanks
> --
> pmud|||Hi Aaron and Geoff,
Thanks for the reply. I made it a datetiem, and in the default value, I used
the function, GetDate() , and it worked. :)
pmud
"Geoff N. Hiten" wrote:

> First, you need to define the field as a datetime data type. The timestam
p
> data type is used for concurrency checking using optomistic locking and do
es
> not represent an actual date and time.
> I typically use a default value of CURRENT_TIMESTAMP to put the current da
te
> and time in a field.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
>
>|||Good. Just FYI, getdate() is the T-SQL specific function. The ANSI
(cross-platform) standard is CURRENT_TIMESTAMP. They are absolutely
equivalent for SQL Server, except using getdate() will provoke the "Wrath of
CELKO" (tm). :)
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:996A9A56-738B-4C38-8BE3-F89BBB68BBD2@.microsoft.com...
> Hi Aaron and Geoff,
> Thanks for the reply. I made it a datetiem, and in the default value, I
> used
> the function, GetDate() , and it worked. :)
>
> --
> pmud
>
> "Geoff N. Hiten" wrote:
>|||Hi geoff,
I had saved my tiable with GetDate() as the default value adn data type as
datetime. Now I changed teh default value to CURRETNT_TIMESTAMP as suggested
by you, but when i click on Save , the default value automatically changes t
o
GetDate() . What do you suggest for this? Can this be a problem?
Thanks
--
pmud
"Geoff N. Hiten" wrote:

> Good. Just FYI, getdate() is the T-SQL specific function. The ANSI
> (cross-platform) standard is CURRENT_TIMESTAMP. They are absolutely
> equivalent for SQL Server, except using getdate() will provoke the "Wrath
of
> CELKO" (tm). :)
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:996A9A56-738B-4C38-8BE3-F89BBB68BBD2@.microsoft.com...
>
>|||> I had saved my tiable with GetDate() as the default value adn data type as
> datetime. Now I changed teh default value to CURRETNT_TIMESTAMP as
> suggested
> by you, but when i click on Save , the default value automatically changes
> to
> GetDate() . What do you suggest for this? Can this be a problem?
This is Enterprise Manager playing tricks on you. My suggestion is to stop
using Enterprise Manager for these things, use Query Analyzer and learn the
T-SQL equivalent(s).
A|||Ok. Thanks Aaron. I will try through Quey Analyzer.
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is Enterprise Manager playing tricks on you. My suggestion is to sto
p
> using Enterprise Manager for these things, use Query Analyzer and learn th
e
> T-SQL equivalent(s).
> A
>
>|||> Ok. Thanks Aaron. I will try through Quey Analyzer.
If you use Enterprise Manager to "verify" be aware that it may still want to
convert CURRENT_TIMESTAMP to GETDATE().

Automatically create rows

Is there a way to automatically insert a row into a table when a row is
created in another table?
For example, suppose a row is added to the "Current Data" table. I would
like another table, "Historical Data", to be automatically updated with data
from from the row added to "Current Data". Is this possible? If so how?
Thanks in advance for any help!Read-up on triggers in SQL Server Books Online. Triggers can be written to
respond to various DML statements and can do operations like inserting into
other tables etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>|||Matt
Lookup CREATE TRIGGER ... ON Table FOR INSERT,UPDATE in the BOL
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>sql

Automatic updating of datetime field

I need to automatically update a datetime field for a record to the current time whenever the record is updated.

create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go

One option would be to use an instead of update trigger.

create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go

update t set value='dick' where id=1 and name='fred'
go

Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.

Any ideas?...also, you don't know what database server it is going to be running on, so it has to be platform-independent.

...and it needs to be fully compatible with the Mayan calendar as well.

...oh yeah, and the final code must be a palindrome that reads the same way forwards as backwards! Yeah, that's it! What a kick-ass application design! Whooooo-eeeeeeee!sql

Auto-Generate MDX Query from Cube OWC Settings?

Can I somehow auto-generate an MDX query based on current settings in the AS OWC cube browser? Why? If so, I will want to use the AS OWC cube browser to filter the data down to < 66K rows, then auto-generate an MDX query from the current browsed settings, copy and paste the MDX script into MS Excel's OLAP query, and use it. I'm trying to avoid the 66K row limitation in Excel 2002.

If this is innappropriate, is there another way, besides raw MDX hand-coding, to accomplish the same thing?

You can start a trace using the SQL Server Profiler, and see the MDX query created by OWC. Then use that query in Excel?

Chris.

Auto update a field with the current date/time

How can I set a column in a table to auto update the date and time everytime something in that row is updated or when the row is first added?

Thanks ahead for the help,

Jason

Check out BOL to see if the timestamp column can solve your requirements.|||I want to be able to query on this and my understanding of timestamp is that it doesn't actually store dates. Is this incorrect?|||

That is correct. You can set the default value for a column to getdate() or getutcdate(). That takes care of the inserts (if the column isn't mentioned in the insert statement).

For the updates, you'll need to write an update trigger.

|||

jasonburrwc84:

I want to be able to query on this and my understanding of timestamp is that it doesn't actually store dates. Is this incorrect?

Trigger is covered in the thread below. Hope this helps.

http://forums.asp.net/thread/1071147.aspx

auto increment in SServer 2005

I want to create a table under sqlserver 2005 with a primary key field as: auto increment concatenated with with the current year.

example:

For year 2007, the primary key field must be like:

02007

12007

22007
.
.
.
3652007
For year 2008, the primary key field must be like:
02008
12008
32008
.
.
.
3652008
etc

Can you help to do it.

Thanks.

HI!,

Sorry for the blank post, my suggestion is to do it from front end. Since you can't use auto number, If you are using stored procedure for inserting then you can keep the logic inside it also.

Hope this will help