MySQL Date format Question

Question

How do i convert 06 Aug 2010 03:41:44 GMT+00:00 to mm/dd/yy time in mysql?

I basically want to sort this SentDate column (the data is populated from an external source). I am ok to do this in c# also.

Answer 1

If you wanted to get a specific format for your datetime fields, you can do something like this:

select 
    concat(
        month(date_field),
        "/",
        day(date_field),
        "/",
        year(date_field)
    ) 
as 
    formatted_date 
from 
    myTable;
order by
    date_field asc;

However, I'm not sure that this works with other field types - might well do though, so can try.

Answer 2

Hang on. Do you want to convert it to mm/dd/yy format, or do you want to sort it by date/time? The two are not conducive to one another.

Sorting

If the column is already a datetime column in MySQL, then you can sort it normally:

var query = db.MyTable.Where(...)
    .OrderBy(row => row.SentDate);

If the column is a string column (varchar), then you will need to convert the data to a DateTime object as you read it from the database, and then sort it in C#. For example:

var query = db.MyTable.Where(...)
    .AsEnumerable()
    .Select(row => new { row, Sent = DateTime.Parse(row.SentDate) })
    .OrderBy(inf => inf.Sent);

The call to AsEnumerable causes the data to be retrieved, so the DateTime parsing and the sorting happens in C# land instead of on the DB.

Converting to mm/dd/yy

One way to convert a DateTime to a string in the format you described is to use .ToString explicitly:

datetime.ToString("MM/dd/yy")

Another is to use the US-American culture:

datetime.ToString("d", CultureInfo.GetCultureInfo("en-US"))

However, the latter appears to use single-digit months.

Once you’ve converted a DateTime to such a string, sorting it makes no sense because you would be sorting it by the first digit of the month — nobody would find that useful.

Answer 3

If you want to convert it in your select statement, use the following:

SELECT DATE_FORMAT(date, '%m/%d/%y')
FROM   TableName

If you want to do it in code.

DateTime dt = Convert.ToDateTime(mySqlCommand.ExecuteScalar());
string strDateTime = dt.ToString("MM/dd/yy");

Hope this helps.