systemdate to be used - SQL Server Forums

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

systemdate to be used

Author

Topic

parry02
Starting Member

18 Posts

Posted - 2006-03-30 : 05:24:07

I'm new to SQL - so I'd like some help please folks !!!
I have a field in a table which I'd like to update everytime a record is created in this table ???
e.g 'field-created' must be updated with systemdate whenever a record is created in a table called CAR_TRANSACTIONS
How do i do it ????

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-30 : 05:50:56

open the table in the design mode in enterprise manager
set default value, of that field to dbo.CurrentDate

Hope this helps

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-30 : 05:53:31

or getdate()

parry02
Starting Member

18 Posts

Posted - 2006-03-30 : 06:06:23

tried that - Getdate() and the other method
but all the records 'field-created' have been set to todays date
wheras before this was blanks ??
How come its updated this field on all records when in fact i have enterd one record today - it should only have added this record
and updated the field called 'field-created '

please help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 06:20:16

It wont affect previously stored data


create table #t(i int)
insert into #t select 1 union all select 45
Go
select * from #t
Go
alter table #t add d datetime default getdate()
Go
insert into #t(i) select 41 union all select 33
Go
select * from #t
Go
drop table #t

Madhivanan

Failing to plan is Planning to fail

parry02
Starting Member

18 Posts

Posted - 2006-03-30 : 06:23:04

i'm sorry I dont understand
WHat do I have to do ???

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-30 : 06:41:51

Can you explain your situation with some sample data so that it can be easy to understand..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 07:49:47

What is the code you used?

Madhivanan

Failing to plan is Planning to fail

parry02
Starting Member

18 Posts

Posted - 2006-03-30 : 07:54:02

I have 4 fields in a table which are created
everytime a record is created ...
(This code resides in another application from which
records are created in CAR_TRANSACTIONS):

INSERT INTO CAR_TRANSACTIONS
(PaymentRef, CarRef, Amount, DateCreated)

VALUES ('{PaymentReference}', '{reference}', {TotalAmount)

INSERT INTO CAR_TRANSACTIONS
SET Transdate = getdate ()

The code is as above and I would like to populate
'Transdate' everytime a record is created ....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 07:58:49

If DateCreated has default value of getdate(), then use

INSERT INTO CAR_TRANSACTIONS
(PaymentRef, CarRef, Amount)
VALUES ('{PaymentReference}', '{reference}', {TotalAmount)

Now apply select statement

Select * from CAR_TRANSACTIONS

Madhivanan

Failing to plan is Planning to fail

parry02
Starting Member

18 Posts

Posted - 2006-03-30 : 08:25:26

I get an error .......

'Incorrect syntax near the keyword 'SET'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 08:36:04

Post the full code you used

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-30 : 08:37:14

As Madhivanan's said. If the DateCreated has default value of getdate() as in the DDL below

create table CAR_TRANSACTIONS
(
	PaymentRef	varchar(10), 
	CarRef		varchar(10), 
	Amount		decimal(10,2), 
	DateCreated	datetime	default getdate()
)

-- You can ommit the DateCreated column in the insert statement
insert into CAR_TRANSACTIONS (PaymentRef, CarRef, Amount)
select	'abc', 'def', 123.45

select * from CAR_TRANSACTIONS

/* RESULT :
PaymentRef CarRef     Amount       DateCreated                                            
---------- ---------- ------------ -----------------------
abc        def        123.45       2006-03-30 21:33:56.480

(1 row(s) affected)
*/

If you do not have the default value, you will have to
insert into CAR_TRANSACTIONS (PaymentRef, CarRef, Amount, DateCreated)
select 'abc', 'def', 123.45, getdate()

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

parry02
Starting Member

18 Posts

Posted - 2006-04-04 : 04:25:22

Thankyou - I have tried that and I think its OK -
I cannot tell as all the records 'datecreated' have nbeen set to todays date ....

Thakyou very much to all ......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources