Date Time field in ASP/SQL Server

[JP SIngh]

Can someone please suggest the correct way to store the date & time in SQL
Server? I want to store the date and time a record was created.

What should the field type be in SQL Server?

How can I insert the current date/time into the table using SQL?

I used to have date/time field type in access and use the following code

currTime = Now()

SQL="Insert into <table> (id, recDate) Values (" & sId & ", #" & currTime &
"#)"

conn.execute(SQL)

Please can someone suggest what the field type I should be using in SQL
Server to store this date and also what should I replace the line
currTime=Now() to?

thanks in advance

[Bob Barrows [MVP]]

JP SIngh wrote:

Can someone please suggest the correct way to store the date & time
in SQL Server? I want to store the date and time a record was created.

What should the field type be in SQL Server?
datetime

How can I insert the current date/time into the table using SQL?
You can set its default to GETDATE().

I used to have date/time field type in access and use the following
code

currTime = Now()

SQL="Insert into <table> (id, recDate) Values (" & sId & ", #" &
currTime & "#)"
You could have simply done:

SQL="Insert into <table> (id, recDate) Values (" & sId & ", Now)"
conn.execute(SQL)

Please can someone suggest what the field type I should be using in
SQL Server to store this date and also what should I replace the line
currTime=Now() to?

thanks in advance

If you set the column's default to GETDATE() then you don't even have to
mention the column in your insert statement.

ALTER TABLE <table> ADD
recDate datetime DEFAULT GETDATE() NOT NULL

SQL="Insert into <table> (id) Values (" & sId & ")"

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand [SQL Server MVP]]

> Can someone please suggest the correct way to store the date & time in SQL

Server? I want to store the date and time a record was created.

What should the field type be in SQL Server?
SMALLDATETIME should be sufficient unless you need sub-minute accuracy (if
so, use DATETIME - just realize that sub-minute accuracy doubles your
storage requirement for that column, as well as index size).
How can I insert the current date/time into the table using SQL?
sql = "INSERT table(idColumn, createdDate) SELECT " & sID & ",
CURRENT_TIMESTAMP)"

You don't need to pass the date/time from ASP to SQL Server, since SQL
Server is fully capable of determining the current date and time.
SQL="Insert into <table> (id, recDate) Values (" & sId & ", #" & currTime
&
"#)"

Likewise, in Access, you could say:

SQL="Insert into <table> (id, recDate) Values (" & sId & ", now())"

A