Converting date to proper format

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

2010-06-24 00:00:00.000

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.

  • SCOPE_IDENTITY() vs. rs.Fields
  • Yet another SQL Pivot
  • Query @xml variable to get a rowset
  • SQL Server Parent/Child Ordering (Possible Recursion?)
  • sql server Bulk insert csv with data having comma
  • sql query to get earliest date
  •             SqlCommand command = new SqlCommand();
                //connection info here
                sql2 = "(select statement here);
                command.CommandText = sql2;
                dates["Entry"] = command.ExecuteScalar();
    

    So, this fills up the grid w/ the correct dates, however, I’m trying to format it so I just get the month, day, and year, not the time after, with slashes. So like this:

    06/24/2010
    

    I’ve tried converting it to date time and using:

    ToString("MM/dd/yyyy")
    

    I’ve tried using a reader instead and doing something like:

    reader.GetDatetime.toString("MM/dd/yyy")
    

    which also didn’t work. I just can’t seem to find the correct way to do this. Any tips would be great.

    Edit: Here’s the sql2 statement:

    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();
    

    This is just getting the most recent date a user has entered stuff in.

    Edit 2:
    The date that shows up in the grid is like so “3/12/2013 12:00:00 AM”.

    Edit 3: The .ExecuteScalar() function seems to be formatting the date automatically to “3/12/2013 12:00:00 AM” type of format.

  • SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified for MDF file
  • How to put an image into selected postition and store in database
  • LINQ query slow, creates Timeout; generated SQL is fine?
  • ASP.Net SessionState using SQL Server - is the data encrypted?
  • Streaming Databased Images Using HttpHandler
  • Using TimeZones on website using ASP.Net 3.5 / SQL Server 2005
  • 4 Solutions collect form web for “Converting date to proper format”

    Just change your format to yyyy-MM-dd like;

    dates["Entry"] = ((DateTime)command.ExecuteScalar()).ToString("yyyy-MM-dd", 
                                                         CultureInfo.InvariantCulture); 
    

    2010-06-24 If you want to display a DateTime like this you just pass a format string of “yyyy-mm-dd” so it would look like this:

    string Date = new DateTime(2010, 6, 24).ToString("yyyy-mm-dd");
    

    You can read more about what formats you can output on a DateTime at this link:
    https://msdn.microsoft.com/en-us/library/zdtaw1bw(v=vs.110).aspx

    You can also use ToShortDateString() function to cut off the time string-:

    reader.GetDatetime.ToShortDateString();
    

    Try out following in the sql:

    select CONVERT(datetime, column_name, 101) from table_name
    

    101 – is datetime style. You may find all styles by the link

    Also in code behind:

    Convert.ToDateTime(reader["column"].ToString()).ToShortDateString();
    

    Hope it helps

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.