How to insert current date and time in sql server database using timestamp datatype?

Question

I want to insert current date and time into sql server database from asp.net webpage. I am using timestamp datatype in sql server table.

table structure:

CREATE TABLE user3
  (
     uname VARCHAR(50) PRIMARY KEY,
     email VARCHAR(50),
     doj   TIMESTAMP
  );

code used :

   cmd.Parameters.Clear();
        cmd.CommandText = "insert into user3 values(@uname,@email,@doj)";
        cmd.Parameters.AddWithValue("@uname", uname);           
        cmd.Parameters.AddWithValue("@email", email);   
        cmd.Parameters.AddWithValue("@jdate", DateTime.Now);
        if (cn.State == ConnectionState.Closed)
            cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();

Please Help me with your suggestions and solutions.

For storing date in database use better [DATETIME](http://stackoverflow.com/questions/7105093/difference-between-datetime-and-timestamp-in-sqlserver). TIMESTAMP has different usage. — Lukasz Szozda, Aug 23 '15 at 15:57
Is there a reason you are using timestamp? That is used for versioning, not for storing a date/time value. — TTeeple, Aug 23 '15 at 15:58
If the value will only ever be set once (as join date will), set the column in the database to have a default value — Mark Tickner, Aug 23 '15 at 16:03
Explicitly name columns in `INSERT INTO user3 (uname, email, doj) VALUES (@uname, @email,@doj)` — Lukasz Szozda, Aug 23 '15 at 16:06
Another solution do not pass date at all, create INSERT and UPDATE trigger to automatically set date. — Lukasz Szozda, Aug 23 '15 at 16:13

score 2 · Accepted Answer · answered Aug 23 '15 at 16:01

2

Timestamp is method for row versioning. It is automatically generated. But datetime is a datatype. So use datetime instead of timestamp.

create table user3(uname varchar(50) primary key,email varchar(50),doj datetime);

answered Aug 23 '15 at 16:01

Sailesh Babu Doppalapudi

1,534
1
10
22

1

Or `datetime2` is better and recommended for new work. – Martin Smith Aug 23 '15 at 16:03
Changing datatype is not all, for full solution look how parameters are binded. – Lukasz Szozda Aug 23 '15 at 16:05
Thanks @Iad2025. I saw that parameter just now. – Sailesh Babu Doppalapudi Aug 23 '15 at 16:10
Don't [fix code inside the question](http://stackoverflow.com/review/suggested-edits/9259307). That's what answers are for. – Martin Smith Aug 23 '15 at 16:20

score 1 · Answer 2 · answered Aug 23 '15 at 16:02

Use datetime with a default value:

create table user3 (
    uname varchar(50) primary key,
    email varchar(50),
    doj datetime default getdate()
   );

When you insert a new row into the table -- with no value for doj -- then it will get set automatically. I usually called this column CreatedAt.

score 0 · Answer 3 · edited May 23 '17 at 11:43

0

Assuming it's the case that you are making an insert every time a user signs up;

Try setting a constraint on the table

create table user3(uname varchar(50) primary key,email varchar(50),doj datetime default getdate());

Then you won't have to pass in that value each time

SQL Server default date time stamp?

edited May 23 '17 at 11:43

Community

1
1

answered Aug 23 '15 at 16:08

Fin

173
1
6

How to insert current date and time in sql server database using timestamp datatype?

3 Answers3