CODEWITHSUNDEEP
c#sql
mnaftal
mnaftal

Reputation: 51

date time now format

Hi I try to insert into DB date time and the Column is date type what I need to do? this is the code

  string query = "INSERT INTO Feedback (user_Name, date_of_, Praise)  VALUES     ('"+TextBox1.Text+"',"+DateTime.Now+",'"+TextBox2.Text+"')";
    SqlCommand cmd = new SqlCommand(query, con);
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();

Upvotes: 0

Views: 667

Answers (7)

SWeko
SWeko

Reputation: 30872

I would advise against using time from the application server to insert values into the database. The most basic example how that can go wrong is that you could have two servers set to different time zones, that use the same database. What server's time is the right time?

Other thing is the neccessary transformation of a datetime to string when you are using inline SQL statements. If the application server and the database server are set to different cultures, you need to be extremely careful not to insert May 2nd (02.05), when you want to insert Feb 5th (02.05).

Sure, all these issues are avoidable, but why bother with them at all, when the RDBMS can do all that for us?

BTW, even if you don't want to use stored procedures, use parameters.
This code should be reformated like:

string query = "INSERT INTO Feedback (user_Name, date_of_, Praise)  VALUES     (@username, getdate(), @praise)"; 

SqlCommand cmd = new SqlCommand(query, con);
SqlParameter param = new SqlParameter("@username", SqlDbType.Text);
param.Value = text1;
cmd.Parameters.Add(param);

param = new SqlParameter("@praise", SqlDbType.Text);
param.Value = text2;
cmd.Parameters.Add(param);

con.Open(); 
cmd.ExecuteNonQuery(); 
con.Close();

Upvotes: 6

Jon Skeet
Jon Skeet

Reputation: 1499800

Don't include the value directly in your SQL.

Use a parameterized query instead. There's no point in messing around with string formatting when the database is quite capable of accepting a prepared statement with a DateTime parameter.

You should get in the habit of using query parameters for all values which can't be simply hard-coded into the SQL to start with. For example, your query is currently just blithely taking the contents of TextBox1.Text and inserting that into the SQL. That's a recipe for a SQL injection attack.

You should separate the code (the SQL) from the data (the values). Parameterized queries are the way to do that.

EDIT: Using a built-in function in the SQL is fine, of course, if you're happy to use the database's idea of "now" instead of your client's idea of "now". Work out which is more appropriate for your situation.

Upvotes: 5

Steven Ryssaert
Steven Ryssaert

Reputation: 1967

Why don't you use a TIMESTAMP column in your database ? Seems like overhead by inserting it through your code.

The following link provides more info:

http://msdn.microsoft.com/en-us/library/aa260631(SQL.80).aspx

edit: Set the default value of your database column as CURRENT_TIMESTAMP (Transact-SQL), and leave the column name out of your insert statement. The current date and time will be inserted by your database automatically. No problem with conversions anymore!

Upvotes: 2

Bazzz
Bazzz

Reputation: 26937

You can use the Date property:

DataTime.Now.Date

Upvotes: 0

linepogl
linepogl

Reputation: 9335

You have to convert your DateTime to an Sql DateTime literal. The easiest way to do it is this:

DateTime.Now.ToString(System.Globalisation.CultureInfo.InvariantCulture)

Yet, especially for DateTime.Now, you may use some Sql function, such as GetDate() but that often depends on your database server.

Upvotes: 0

Elzo Valugi
Elzo Valugi

Reputation: 27856

There is a NOW() function in most SQL implementations.

Upvotes: 1

Øyvind Bråthen
Øyvind Bråthen

Reputation: 60694

Replace DateTime.Now with DateTime.Now.ToString("yyyy-MM-dd");

Also, you should really parameterize your insert statement so that you cannot fall victim of a SQL injection attack.

Upvotes: 1

Related Questions