c# mysql 时间_.net c# MySql中的日期时间

Trying to get data from a MySql DB using C#.

The column value of datatye 'DATE' in table is like '10/11/2014 12:00:00 AM'. But I would like to only have 10/11/2014 (or 2014-10-11)

But this isn't working.

Select CAST(DumpDate AS DATE), ResourceName, Sum(ActualEffort)

from Timesheet_Data where dumpdate >= '2014-10-11'

group by DumpDate, ResourceName

This also didnt work - Select DATE(DumpDate)

The result value is still '10/11/2014 12:00:00 AM'.

I do not have access to this db, so cant check if the column is date or varchar or datetime. Going by the given document it says the datatype of 'DumpDate' is 'Date.

C#:

private static DataTable GetEffortDataFromMySqlDB()

{

DataTable dtEffort = new DataTable();

try

{

string CmdText = "select CAST(DumpDate AS DATE), ResourceName,

Sum(ActualEffort) from Timesheet_Data where

dumpdate >= '2014-10-11' group by DumpDate,

ResourceName";

DataSet ds = MySqlHelper.ExecuteDataset(BaseDB.MySqlConnectionString,

CmdText);

dtEffort = ds.Tables[0];

}

catch (Exception ex)

{

}

return dtEffort;

}

Talk1:

"this isn't working" doesn't give us nearly enough information. What is the field type in your database? If it's DATE then whatever representation happens to be shown in whatever you're using to see the data (you haven't said) is irrelevant - the value is just a date. It's also not at all clear what you mean by "the result value" - what result value? What does your C# code look like? Please provide more information.

Talk2:

colum val 10/11/2014 12:00:00 AM meaning its not date datatype rather varchar.

Talk3:

the datatype of the column is 'date'. I mean the output by result value

Solutions1Select DATE_FORMAT(DumpDate, '%D/%m/%Y') AS theDate, ResourceName, Sum(ActualEffort)

from Timesheet_Data where dumpdate >= '2014-10-11'

group by DumpDate, ResourceName

Or

Select DATE_FORMAT(DumpDate, '%Y-%m%-d') AS theDate, ResourceName, Sum(ActualEffort)

from Timesheet_Data where dumpdate >= '2014-10-11'

group by DumpDate, ResourceName

Talk1:

For both of them it says - You have an error in your SQL syntax

Talk2:

I am sorry, I forgot the quotes, chec my edit

Solutions2

You need to use CONVERT instead CAST:

Select CONVERT(DATE,DumpDate), ResourceName, Sum(ActualEffort)

from Timesheet_Data where dumpdate >= '2014-10-11'

group by DumpDate, ResourceName