CODEWITHSUNDEEP
c#mysql
sukesh
sukesh

Reputation: 2523

Datetime to date in 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;
        }

Upvotes: 0

Views: 78

Answers (2)

user3522371
user3522371

Reputation: 

Select 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

Upvotes: 1

oussama abdou
oussama abdou

Reputation: 327

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

Upvotes: 1

Related Questions