Insert, select and update DateTime

问题:

I have a table with time the column named time and the datatype is Date.

In asp.net I want a query to insert the date, and another so select between 2 date.

I already try this:

string data = DateTime.Now.ToShortDateString(); //date= 28/10/2014 -> dd/mm/yyyy
string comando = "INSERT INTO example (date) values '" +data+ "+"'";

And I used that query to select between 2 dates

select * 
from example 
where date >= '25/10/2014' and date <= '28/10/2014'

I already tried with datatype varchar but it doesn't work. So I'm trying with the datatype date.

But when I'm executing the query INSERT I get a error

Conversion failed when converting date and/or time from character string.

Somebody please can help me? The only problem I have is to save the time into the table.

If possible I want that format of the date: dd/mm/yyyy

Update:

I'm having problem with update a row that have a date(date) and a varchar(name) That is my code:

string comando = "UPDATE example set name=@name WHERE data = @date";
SqlCommand cmd = new SqlCommand(comando, connect);
cmd.Parameters.Add("@name", nome);
cmd.Parameters.Add("@date", SqlDbType.Date).Value = data;
cmd.ExecuteNonQuery();

The messange error is: "String or binary data would be truncated. The statement has been terminated."

回答1:

You should NEVER concatenate together your SQL commands like you do! This opens them up to SQL injection attacks.

Instead - use parameters! This also gets rid of a lot of conversion issues.

So in your case, you should use:

string comando = "INSERT INTO example (date) VALUES (@DateParam)";

and then you need to set the @DateParam on your SqlCommand:

cmd.Parameters.Add("@DateParam", SqlDbType.Date).Value = YourDataValueHere

and that should take care of all your issues!

If you want to select - again, use parameters!

select * 
from example 
where date >= @fromDate and date <= @toDate

when you run this from C#.

If you use T-SQL directly (in Mgmt Studio), then use the ISO-8601 format YYYYMMDD which is indepdent of any dateformat and/or language settings -

select * 
from example 
where date >= '20141025' and date <= '20141028'

This works on any version of SQL Server and with any dateformat, language and regional settinsg.

回答2:

If the table really is a DATE/DATETIME/DATETIME2/SMALLDATETIME, you'd be better off doing something more like:

using (SqlCommand cmd = new SqlCommand("INSERT INTO example (date) values (@param)"))
{
    cmd.Paramters.Add("@param", SqlDbType.Datetime).Value = DateTime.Now;

    cmd.ExecuteNonQuery();
}

Similarly, when you query the table, something more like:

using (SqlCommand cmd = new SqlCommand("SELECT * FROM example WHERE date BETWEEN @FromDate AND @ToDate"))
{
    cmd.Paramters.Add("@FromDate", SqlDbType.Datetime).Value = DateTime.Now;
    cmd.Paramters.Add("@ToDate", SqlDbType.Datetime).Value = DateTime.Now; // Of course, you'd probably want to pass through values as parameters to your method

    // Fill your dataset/get your SqlDataReader, etc. as preferred
}