How to set datetime?>

I've mined through the support files but without finding an answer to my
question.. In Access, I used to use the paramter Get() to have a datetime
field to default to the server's date/time each time a new record was
inserted. Anyone know how I can do this in SQL Server?Default the column to GETDATE()
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||CREATE TABLE blat
(
id INT,
dt SMALLDATETIME DEFAULT GETDATE()
)
GO
INSERT blat(id) SELECT 1
GO
SELECT * FROM blat
GO
DROP TABLE blat
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||I'm assuming you mean on the Formula line.. but doing that changes ALL
entries in the table in the DATETIME field to the same entry! (the
latest)... each entry should be unique..|||How do you expect to back-fill rows that already exist?
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:%23BYqwausEHA.1400@.TK2MSFTNGP11.phx.gbl...
> I'm assuming you mean on the Formula line.. but doing that changes ALL
> entries in the table in the DATETIME field to the same entry! (the
> latest)... each entry should be unique..
>|||This is a new table, so all data entered so far is strictly garbage data
that will be removed before anything goes live..|||The DEFAULT constraint will be evaluated upon insertion; so if you have your
DEFAULT set to GETDATE(), the current date at the time the row is inserted
is used.
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..
>|||Then why do you care what is being applied to the existing data, if it's
just going to be thrown away?
Anyway, don't use the formula line, and STOP "designing" tables in
Enterprise Manager.
To quote myself:
...
There are subtle differences in what happens to rows that existed prior to
the column addition, depending on whether you define the new column as NULL
or NOT NULL.
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME NOT NULL DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 1
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 2
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
Note the slight difference in the output.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..