Automatic insert a now datetime to a column whenever a new row...

[whloo]

Hi,

I am using a SQL Server db as my backend.
How can i automatically insert a now datetime into my datecreated column whenever i insert a new record into the table.
if i am not wrong, i need to put a sql formula in the FORMULA property for my datecreated field right?
if so, what is the correct syntax?
I tried getToday()....
but it is incorrect because it will always change my other records to the today date time.
i only want it affect only the new inserted record.
Any help will be greatly appreciated.
Thanks!

Regards,
weihann.

 

[clarkin]

you have two options:

1. use your current table with your datecreated column (of type datetime).
When you insert values use GETDATE() to insert the current date/time eg:

INSERT INTO myTable (column1, column2, datecreated)
VALUES ('var1', 'var2', GETDATE())

2. create a default for the datecreated column so it defaults to GETDATE() - if you are using Query Analyser to create your table you can just amend it to include the default like so:

CREATE TABLE myTable (
column1 varchar(10) NULL,
column2 varchar(10) NULL,
datecreated datetime NOT NULL DEFAULT GETDATE()
)

Then you can ignore the datecreated column when you insert, and it will by default add the current date/time:

INSERT INTO myTable (column1, column2)
VALUES ('var1', 'var2')

Hope that clears it up for you


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[whloo]

thanks clarkin!!!
i am so stupid.
i made a very stupid mistake.
i put the getdate() into the formula property.
it shld be in default property!
thanks clarkin!!!!