Sql server 2005 Datatype for Date

问题:

I want to create a table with DATE Column. For that column I am using DateTime datatype .

Is there any other datatype or option to display only date without Time. am using Sql Server 2005

Have any format to use?

Otherwise if i use Datetime datatype, how can I retrieve in c# with Update query.

I use C# code how to retrieve only date

SqlCommand cmd = new SqlCommand(("Update tablename set Date = convert(GETDATE(),103) where Name ='" + TextBox1.Text + "' "), con);

回答1:

You have to use Datetime. Take a look at this chart.

回答2:

SELECT replace(convert(varchar, getdate(), 111), '/','-' ) -> YYYY-MM-DD

Have a look at How to format datetime & date in Sql Server 2005

回答3:

You can use SQL Server formatting functions with convert functions. Here is the link where you can find all the formatting functions.

http://www.sql-server-helper.com/tips/date-formats.aspx

If you use SQL Server 2008 then only you can use date type.

回答4:

The DATE data type was not introduced until SQL Server 2008. As a datatype, it is not part of 2005. You have to store it as a DATETIME and then use a conversion of some sort to strip the time part off.

Also, SQL Server 2005 is no longer a supported platform. You should really consider upgrading to a newer platform anyway.

回答5:

Follow this pattern,

const string updateTable = "UPDATE [dbo].[Tablename]
    SET [Date] = getutcdate()
    WHERE [Name] = @Name;"

using (var connection = new SqlConnection(youConnectionString))
{
    using(var command = new SqlCommand(updateTable, connection))
    {
        command.Parameters.AddWithValue("@Name", TextBox1.Text);
        connection.Open();
        command.ExecuteNonQuery();
    }
}