CODEWITHSUNDEEP
c#sqldatetime
Carsten Løvbo Andersen
Carsten Løvbo Andersen

Reputation: 27041

Insert Date into sql table with Date column

Hello and thanks for reading.

I'm trying to insert the current date into my table, but I can't figure out how to write it correctly.

Here is my C# code:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
conn.Open();

string Comment = UserWriteComment.Text;
string ID = DetailedID.Text;
string Name = DetailedName.Text;
string UniqueID = lblID.Text;


string query = "INSERT INTO Comment(TicketID, Name, Comments, UserID, Date)" + "Values('" + ID + "', '" + Name + "', '" + Comment + "', '" + UniqueID + "', '" + Date + "')";
using (SqlCommand com = new SqlCommand(query, conn))
{
    com.ExecuteNonQuery();
    UserWriteComment.Text = "";
}

In the Query, There is a value called Date. This is here I like the Function to pass the current date into my Table.

I hope you can help me because I didnt managed to find the answer anywere.

Thanks:)

Upvotes: 1

Views: 16405

Answers (4)

Sean
Sean

Reputation: 1

Date inserts for SQL Server is best used via :

GetDate()

or

Convert(Varchar, GetDate(), 101)

Note: converting the GetDate() value to varchar type 101 shortens the value to just the date w/o time stamp.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460018

Use DateTime.Now or (in the database via sql) GetDate(). But more important, use sql-parameters to prevent sql-injection and conversion/localization issues:

string insertSql = @"INSERT INTO Comment(TicketID, Name, Comments, UserID, Date)
                     Values(@ID, @Name, @Comment, @UniqueID, @Date)";
using (var conn = new SqlConnection("...."))
using (var com = new SqlCommand(insertSql, conn))
{
    com.Parameters.AddWithValue("@ID", ID);
    com.Parameters.AddWithValue("@Name", Name);
    com.Parameters.AddWithValue("@Comment", Comment);
    com.Parameters.AddWithValue("@UniqueID", UniqueID);
    com.Parameters.AddWithValue("@Date", DateTime.Now);
    conn.Open();
    com.ExecuteNonQuery();
}

The using-statement ensures that unmanaged resources like the connection will be disposed/closed even in case of an error.

Upvotes: 7

Shiva
Shiva

Reputation: 20935

Use DateTime.Now instead of Date. i.e. update the INSERT line to the following.

string query = "INSERT INTO Comment(TicketID, Name, Comments, UserID, Date)" 
              + "Values('" + ID + "', '" + Name + "', '" + Comment + "', '" 
              + UniqueID + "', '" + DateTime.Now + "')";

P.S: You really should be using Parameterize statements to avoid a Bobby Tables situation.

enter image description here

To fix this, implement it as shown by @Tim in his answer:

Upvotes: 5

Jag
Jag

Reputation: 291

Instead of Date, try using the following

DateTime.Now

Another function that can help you is 

 GETDATE()

Upvotes: 0

Related Questions