How to add date in SQL Server database in correct way?

Question

I'm trying for two days to add date in my database table but every time I see this 12:00:00 AM value instead of actual time. I tried enough for this simple issue.

http://puu.sh/cpP2l/0b0dd66042.png

obj.Payment_date = DateTime.Now.ToString("G");

using (SqlCommand command = new SqlCommand("INSERT into Payments([software_id] ,[email] ,[ip] ,[payment_status] ,[payment_date] ,[transaction_id] ,[amount] ,[license_type] ,[license_status] ,[cd_key]) VALUES(@software_id ,@email ,@ip ,@payment_status ,@payment_date ,@transaction_id ,@amount ,@license_type ,@license_status ,@cd_key)", con))
{
    command.Parameters.Add(new SqlParameter("software_id", obj.Software_id)); 
    command.Parameters.Add(new SqlParameter("email", obj.Email));
    command.Parameters.Add(new SqlParameter("ip", obj.IP));
    command.Parameters.Add(new SqlParameter("payment_status",obj.Payment_status)); 
    command.Parameters.Add(new SqlParameter("payment_date", obj.Payment_date));
    command.Parameters.Add(new SqlParameter("transaction_id", obj.Transaction_id));
    command.Parameters.Add(new SqlParameter("amount", obj.Amount));
    command.Parameters.Add(new SqlParameter("license_type", obj.License_type));
    command.Parameters.Add(new SqlParameter("license_status",obj.License_status));
    command.Parameters.Add(new SqlParameter("cd_key", obj.Cd_key));

    if (command.ExecuteNonQuery() > 0)
    {
    }
}

The payment_date column must be of type DATETIME in the database. The obj.Payment_date should be a DateTime type, not a string. — Remus Rusanu
– Remus Rusanu, Commented Oct 25, 2014 at 11:40

Manoj · Accepted Answer · 2014-10-25 11:21:50Z

2

Make Sure you have created Payment_date with DateTime Data type.

and set Date & Time like this:

obj.Payment_date= DateTime.Now

answered Oct 25, 2014 at 11:21

Manoj

6314 silver badges9 bronze badges

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

Comments

Giorgos Betsos · Accepted Answer · 2014-10-25 11:46:30Z

1

If what you want is to record the date and time by the time of insertion then you'd better add a default constraint to your column:

ALTER TABLE Payments
ADD CONSTRAINT df_CurrentDateTime
DEFAULT CURRENT_TIMESTAMP FOR [Payment_Date]

In this case you do not need to provide a parameter for Payment_Date field at all.

EDIT: If the above solution is not suitable for your needs, then I would suggest providing the parameter type for the Payment_Date parameter used by your Command Object.

e.g. try sth like:

SqlParameter myParam = new SqlParameter("payment_date", obj.Payment_date);   
myParam.DbType = System.Data.DbType.DateTime;
command.Parameters.Add(myParam);

edited Oct 25, 2014 at 11:46

answered Oct 25, 2014 at 11:24

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

2 Comments

m.qayyum Over a year ago

Great, Thanks Giorgos Betsos

Satyajit Over a year ago

The data type of these parameters are not specified which is causing the issue I think. It's always a good practice to specify the data type values in SQL parameters :)

Collectives™ on Stack Overflow

How to add date in SQL Server database in correct way?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related