Date Time field in ASP/SQL Server

J

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
 
B

Bob Barrows [MVP]

JP said:
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 said:
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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top