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]]

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:

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

 

[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