Issues converting strings to datetime

Question

I have a table in an ms-sql database which contains dates in a column like so:

2012-10-31 00:00:00.000

This column is listed as type 'datetime'. As in, in ms sql server management studio, I expand the table, then columns, and see the Dates(PK, datetime, not null).

Then in c#, I get those dates w/ a statement and assign it to a variable which populates a column of a grid on a web page made w/ asp.net. The sql statement is just getting the last entry of a user.

        SqlCommand command = new SqlCommand();
        //connection info here
        sql2 = "select max(day) as day from users u join days d on d.User_ID = u.id where u.ActiveUser = 1 and u.id = " + Users["ID"].ToString();;
        command.CommandText = sql2;
        dates["Entry"] = command.ExecuteScalar();

This populates the dates in the column like so:

10/31/2012 12:00:00 AM

Now, I have no idea why it's getting converted like that. I was following the data while be debugging and it changes w/ the command.ExecuteScalar() function. The format I would like to have is:

10/31/2012

without the hh/mm/ss AM.

I could not get ANY DateTime conversions/parses/formatting to work properly. I've spent four days on this. I asked a similar question here: Converting date to proper format , trying all the different suggestions.

What I'm doing in the meantime is:

string dt = command.ExecuteScalar().ToString();
dt = dt.Substring(0, dt.IndexOf(" ") + 1); 
dates["Entry"] = dt;

Which gets rid of all the text after the space in the date string that was returned. This would be okay, except that I wanted to compare these dates I'm getting, so I need them in DateTime. Trying to convert these strings to DateTime just would not work.

Edit: Definitions and types -

DataRow dates;
//for loop here going through for each user
dates = _dtuserhours.NewRow(); // _dtuserhours is a DataTable
//Sql statements here
_dtuserhours = new DataTable("newtable");
DataColumn column;
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Entry";
_dtUserHours.Columns.Add(column);

Answer 1

First, you need to change your SQL string to use command parameters, otherwise you are open to a SQL Injection attack.

Next, You can shape the output of your DateTime field in SQL Server to output the format you are looking for. What you are getting is the default format for DateTime depending on your culture.

Or, you could easily just use the string formatter property of the DateTime object that is returned like this:

dates["Entry"] = ((DateTime)command.ExecuteScalar()).ToString("MM/dd/yyyy");

EDIT: Try this instead:

dynamic result = command.ExecuteScalar();
dates["Entry"] = result.ToString("MM/dd/yyyy");

EDIT of the EDIT - set to dynamic because the compiler doesn't know what ExecuteScalar is returning at compile time.