By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,834 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Date Time field in ASP/SQL Server

P: n/a
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

Aug 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Aug 22 '05 #2

P: n/a
> 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
Aug 24 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.