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. TIMESTAMP has different usage. — Lukasz Szozda
– Lukasz Szozda, Commented Aug 23, 2015 at 15:57
Is there a reason you are using timestamp? That is used for versioning, not for storing a date/time value. — TTeeple
– TTeeple, Commented Aug 23, 2015 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
– Mark Tickner, Commented Aug 23, 2015 at 16:03
Explicitly name columns in INSERT INTO user3 (uname, email, doj) VALUES (@uname, @email,@doj) — Lukasz Szozda
– Lukasz Szozda, Commented Aug 23, 2015 at 16:06

Sailesh Babu Doppalapudi · Accepted Answer · 2015-08-23 16:01:45Z

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, 2015 at 16:01

Sailesh Babu Doppalapudi

1,5561 gold badge10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Martin Smith Over a year ago

Or datetime2 is better and recommended for new work.

Lukasz Szozda Over a year ago

Changing datatype is not all, for full solution look how parameters are binded.

Sailesh Babu Doppalapudi Over a year ago

Thanks @Iad2025. I saw that parameter just now.

Martin Smith Over a year ago

Don't fix code inside the question. That's what answers are for.

Gordon Linoff · Accepted Answer · 2015-08-23 16:02:06Z

1

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.

answered Aug 23, 2015 at 16:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:43:55Z

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, 2017 at 11:43

CommunityBot

11 silver badge

answered Aug 23, 2015 at 16:08

Fin

1831 silver badge6 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related