MySql中的日期时间

Question

尝试使用MySql从C#数据库获取数据。表中datatye 'DATE‘的列值类似于'10/11/2014 12:00 AM’。但我希望只有2014年10月11日(或2014-10-11)

但这不管用。

Select CAST(DumpDate AS DATE), ResourceName, Sum(ActualEffort) 
from Timesheet_Data  where dumpdate >= '2014-10-11' 
group by DumpDate, ResourceName

这也不起作用- Select DATE(DumpDate)

结果值仍然是'10/11/2014 12:00:00 AM‘。

我无法访问此db，因此无法检查该列是日期、varchar还是datetime。在给定的文档中，它说'DumpDate‘的数据类型是'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;
        }

Answer 1

Select DATE_FORMAT(DumpDate, '%D/%m/%Y') AS theDate, ResourceName, Sum(ActualEffort) 
from Timesheet_Data  where dumpdate >= '2014-10-11' 
group by DumpDate, ResourceName

或

Select DATE_FORMAT(DumpDate, '%Y-%m%-d') AS theDate, ResourceName, Sum(ActualEffort) 
from Timesheet_Data  where dumpdate >= '2014-10-11' 
group by DumpDate, ResourceName

Answer 2

您需要使用转换而不是强制转换：

Select CONVERT(DATE,DumpDate), ResourceName, Sum(ActualEffort) 
from Timesheet_Data  where dumpdate >= '2014-10-11' 
group by DumpDate, ResourceName