DATE datatype of SQL Server 2008 returing time!

I have a column of datatype DATE in SQL Server 2008, which stores only the date in format "yyyy-MM-dd" but when I select the date from database via C# and converting into string, it shows the time part also like.

"2012-04-21 12:00:00"

what is the problem I didn't store time in database here is my query!

string sql = @"select card_no, amount, csc,expdate " + 
              "from user_account_card " + 
          开发者_运维问答    "where user_id = '" + loginsession.Auser + "';";

SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader red = cmd.ExecuteReader();

while (red.Read())
{
   loginsession.Cardno = Convert.ToString(red["card_no"]);
   loginsession.Cardamount = Convert.ToString(red["amount"]);
   loginsession.Csc=Convert.ToString(red["csc"]);
   loginsession.Expdate = Convert.ToString(red["expdate"]);//date part
   break;
}

MessageBox.Show("database value from database--" +loginsession.Expdate);

Please help me what to do

If the time is showing as midnight then for all intents and purposes it is storing it without time.

SQL has a date data type that does not include time, but you need to re-design the table. Not sure if you need/want to do that.

You could also try this:

((Date)red["expdate"]).ToString();

Since this will convert to a Date data type and not a DateTime data type you should just see the date part in the returned string.

Convert.ToString doesn't have a "date" overload, only "datetime"

SQL Server 2008 will store just date in a DATE column - you can easily verify that in SQL Server Management Studio. Also: a DATE column doesn't store a date in a particular string format - that's just how you see it. A date is a date is a date - it's stored as a date and doesn't have any "format" per se.

However, .NET doesn't have a "date-only" datatype, so reading a DATE column from SQL Server ends up in a DateTime variable in .NET which contains DATE and TIME.

So it's really not an issue of SQL Server here - but rather an issue in .NET