using 2 datetime picker date range in c# with mysql

Question

HI I am trying to use this query to retereive results from mysql data table where i choose dates from datetime pickers but i am not getting the ouptput as required , can you helpme with this.when I try this I get the output on single date but where I required is datewise(daya by day accepted images, rejected images and change images count)

MySqlDataAdapter sda = new MySqlDataAdapter(String.Format(
    "SELECT   datetime ," +
    "sum(CASE action when '2' then 1 else 0 end) as AcceptedIMG, " +
    "sum(CASE action when '3' then 1 else 0 end) as RejectedIMG, " +
    "sum(CASE action when '4' then 1 else 0 end) as ChangeIMG " +
    "FROM customer_1.audit_trail " +
    "where datetime BETWEEN '{0}' and '{1}'",

    start.Value.ToString("yyyy/MM/dd"), 
    end.Value.ToString("yyyy/MM/dd")
), conn)

Iftikar Hussain · Answer

you have missed group by in first condition. try like this if (comboBox1.SelectedIndex == 0)//(Review Images) { MySqlConnection conn = new MySqlConnection(;Convert Zero Datetime=true;); DataTable db = new DataTable(); MySqlDataAdapter sda = new MySqlDataAdapter(String.Format(SELECT DATE(datetime) as date + Sum((CASE action when '2' then 1 else 0 end)) as AcceptedIMG  + Sum((CASE action when '3' then 1 else 0 end)) as RejectedIMG  + Sum((CASE action when '4' then 1 else 0 end)) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= DATE('{0}') and DATE(datetime) GROUP BY (dateonly)  start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd)) conn); sda.Fill(db); dataGridView1.DataSource = db; } else if (comboBox1.SelectedIndex == 1)//(Contravention Images) { MySqlConnection conn = new MySqlConnection(Convert Zero Datetime=true;); DataTable db = new DataTable(); MySqlDataAdapter sda = new MySqlDataAdapter(SELECT date(datetime) as dateonly  + sum(CASE action when '23' then 1 else 0 end) as AcceptedViolation + sum(CASE action when '24' then 1 else 0 end) as RejectedViolation + sum(CASE action when '25' then 1 else 0 end) as SkippedViolation  + FROM customer_1.audit_trail  + GROUP BY (dateonly) + ORDER BY (dateonly) asc  conn); sda.Fill(db); dataGridView1.DataSource = db; } Regards Iftikar

kiran vontela · Answer

Thanks Iftikar you made my day.. thats resolved...... thanks a lot for your help..........

Iftikar Hussain · Answer

are you getting same result while executing directly in mysql? Regards Iftikar

kiran vontela · Answer

01/05/2013 8413 2309 8100 02/05/2013 7772 1467 6944 03/05/2013 9407 1662 9882 04/05/2013 14928 3236 14272 this is the output i get but reterives only data for 01/05/2013 - 31/05/2013. not considering the end datetime picker.

kiran vontela · Answer

SELECT DATE(datetime) as date + Sum((CASE action when '2' then 1 else 0 end)) as AcceptedIMG  + Sum((CASE action when '3' then 1 else 0 end)) as RejectedIMG  + Sum((CASE action when '4' then 1 else 0 end)) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= DATE('{0}') and DATE(datetime) start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd)) conn); GROUP BY day(datetime) -------this resolves the issue but picksup the data only for the start value in date time picker(i.e selected month sum of everyday ) 1-30 days only one month.

kiran vontela · Answer

public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender EventArgs e) { if (comboBox1.SelectedIndex == 0)//(Review Images) { MySqlConnection conn = new MySqlConnection(;Convert Zero Datetime=true;); DataTable db = new DataTable(); MySqlDataAdapter sda = new MySqlDataAdapter(String.Format(SELECT DATE(datetime) as date + Sum((CASE action when '2' then 1 else 0 end)) as AcceptedIMG  + Sum((CASE action when '3' then 1 else 0 end)) as RejectedIMG  + Sum((CASE action when '4' then 1 else 0 end)) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= DATE('{0}') and DATE(datetime) start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd)) conn); sda.Fill(db); dataGridView1.DataSource = db; } else if (comboBox1.SelectedIndex == 1)//(Contravention Images) { MySqlConnection conn = new MySqlConnection(Convert Zero Datetime=true;); DataTable db = new DataTable(); MySqlDataAdapter sda = new MySqlDataAdapter(SELECT date(datetime) as dateonly  + sum(CASE action when '23' then 1 else 0 end) as AcceptedViolation + sum(CASE action when '24' then 1 else 0 end) as RejectedViolation + sum(CASE action when '25' then 1 else 0 end) as SkippedViolation  + FROM customer_1.audit_trail  + GROUP BY (dateonly) + ORDER BY (dateonly) asc  conn); sda.Fill(db); dataGridView1.DataSource = db; } }

Iftikar Hussain · Answer

share me the complete code? how you are binding the output? Regards Iftikar

kiran vontela · Answer

Hi Iftikar you left me middle of the ocean mate....... we are almost there to be solved

kiran vontela · Answer

MySqlDataAdapter sda = new MySqlDataAdapter(String.Format( SELECT DATE(datetime) as Datetime  + SUM((CASE action when '2' then 1 else 0 end)) as AcceptedIMG  + SUM((CASE action when '3' then 1 else 0 end)) as RejectedIMG  + SUM((CASE action when '4' then 1 else 0 end)) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= '{0}' and DATE(datetime)  GROUP BY DATE(datetime)' start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd) )conn);

Iftikar Hussain · Answer

yes share the c# code Regards Iftikar

kiran vontela · Answer

working fine in mysql but not in c#

kiran vontela · Answer

2013-07-01 2563 919 2594 2013-07-02 4614 893 3605 2013-07-03 2888 379 2257 2013-07-04 5997 1831 5228 2013-07-05 4218 758 4041 2013-07-06 3104 836 2943 2013-07-07 0 0 0 2013-07-08 1648 337 1861 2013-07-09 6368 1431 5419 2013-07-10 5109 1449 4018 2013-07-11 3748 1130 5073 2013-07-12 4363 1065 4370 2013-07-13 1873 334 1926 2013-07-14 0 0 0 2013-07-15 2938 1127 2828 2013-07-16 4326 1108 4859 2013-07-17 3662 1094 4411 2013-07-18 6035 1643 5242 2013-07-19 4193 1378 5496 2013-07-20 2218 960 2724 2013-07-21 0 0 0 2013-07-22 2400 621 3142 2013-07-23 3869 1132 4724 2013-07-24 3502 878 5175 2013-07-25 6452 1457 5596 2013-07-26 6129 1732 5466 2013-07-27 1571 693 2266 2013-07-28 0 0 0 2013-07-29 3085 838 2671 2013-07-30 4954 1163 5706 2013-07-31 2409 498 2351

Iftikar Hussain · Answer

now run this SELECT DATE([datetime]) as Datetime1 SUM((CASE action when '2' then 1 else 0 end)) as AcceptedIMG SUM((CASE action when '3' then 1 else 0 end)) as RejectedIMG SUM((CASE action when '4' then 1 else 0 end)) as ChangeIMG FROM customer_1.audit_trail where DATE([datetime])>= '2013-07-01' and DATE([datetime]) GROUP BY DATE([datetime]) Regards Iftikar

kiran vontela · Answer

working fine..

kiran vontela · Answer

2013-07-01 2563 2013-07-02 4614 2013-07-03 2888 2013-07-04 5997 2013-07-05 4218 2013-07-06 3104 2013-07-07 0 2013-07-08 1648 2013-07-09 6368 2013-07-10 5109 2013-07-11 3748 2013-07-12 4363 2013-07-13 1873 2013-07-14 0 2013-07-15 2938 2013-07-16 4326 2013-07-17 3662 2013-07-18 6035 2013-07-19 4193 2013-07-20 2218 2013-07-21 0 2013-07-22 2400 2013-07-23 3869 2013-07-24 3502 2013-07-25 6452 2013-07-26 6129 2013-07-27 1571 2013-07-28 0 2013-07-29 3085 2013-07-30 4954 2013-07-31 2398

Iftikar Hussain · Answer

run this query directly in mysql and check the result SELECT DATE([datetime]) as Datetime1 SUM((CASE action when '2' then 1 else 0 end)) as AcceptedIMG FROM customer_1.audit_trail where DATE([datetime])>= '2013-07-01' and DATE([datetime]) GROUP BY DATE([datetime]) Regards Iftikar

kiran vontela · Answer

04/07/2013 94152 25476 97454

Iftikar Hussain · Answer

how many record you are getting if you use like this MySqlDataAdapter sda = new MySqlDataAdapter(String.Format( SELECT DATE( datetime) as Datetime  + SUM((CASE action when '2' then 1 else 0 end)) as AcceptedIMG  +  SUM( (CASE action when '3' then 1 else 0 end)) as RejectedIMG  +  SUM( (CASE action when '4' then 1 else 0 end)) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= '{0}' and DATE(datetime) GROUP BY DATE( datetime) ' start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd) )conn); Regards Iftikar

kiran vontela · Answer

MySqlDataAdapter sda = new MySqlDataAdapter(String.Format( SELECT datetime  + (CASE action when '2' then 1 else 0 end) as AcceptedIMG  + (CASE action when '3' then 1 else 0 end) as RejectedIMG  + (CASE action when '4' then 1 else 0 end) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= '{0}' and DATE(datetime) start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd) )conn); datetime accepted img rejected img ChageIMG 10/06/2013 18:04 0 1 0 10/06/2013 18:04 0 1 0 10/06/2013 18:04 0 1 0 11/06/2013 00:09 0 0 0 11/06/2013 00:09 0 0 0

Iftikar Hussain · Answer

is it showing same datetime? can you share some output? Regards Iftikar

kiran vontela · Answer

all the records picked up from datetimepicker1 and datetime picker 2 dates (1000 rows approx)

Iftikar Hussain · Answer

without sum how many records you are getting? Regards Iftikar

kiran vontela · Answer

no luck its still the same one row with sum of all columns.

Iftikar Hussain · Answer

you need to get for datetime group? try like this MySqlDataAdapter sda = new MySqlDataAdapter(String.Format( SELECT datetime  + Sum((CASE action when '2' then 1 else 0 end)) as AcceptedIMG  + Sum((CASE action when '3' then 1 else 0 end)) as RejectedIMG  + Sum((CASE action when '4' then 1 else 0 end)) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= '{0}' and DATE(datetime) start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd) GROUP BY (DATE(dateonly))) conn); Regards Iftikar

kiran vontela · Answer

it adds all the column and select the start date . and sum(acceptedimage)sum(rejected image)... 01/06/2013 00:09 111714 31112 112144

Iftikar Hussain · Answer

try like this MySqlDataAdapter sda = new MySqlDataAdapter(String.Format( SELECT datetime  + Sum((CASE action when '2' then 1 else 0 end)) as AcceptedIMG  + Sum((CASE action when '3' then 1 else 0 end)) as RejectedIMG  + Sum((CASE action when '4' then 1 else 0 end)) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= '{0}' and DATE(datetime) start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd) GROUP BY (dateonly)) conn); Regards Iftikar

kiran vontela · Answer

now I can see all the dates chosen from datetimepicker with values as 1 and 0 some what near but want to get sum of the images per day instead of 0 and 1.

kiran vontela · Answer

I changed the code MySqlDataAdapter sda = new MySqlDataAdapter(String.Format( SELECT datetime  + (CASE action when '2' then 1 else 0 end) as AcceptedIMG  + (CASE action when '3' then 1 else 0 end) as RejectedIMG  + (CASE action when '4' then 1 else 0 end) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= '{0}' and DATE(datetime) start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd) GROUP BY (dateonly)) conn);

Iftikar Hussain · Answer

so now you are getting the result. you can set by using CustomFormat property of datetime picker. dateTimePicker1.Format = DateTimePickerFormat.Custom; dateTimePicker1.CustomFormat = yyyy/MM/dd; Regards. Iftikar

kiran vontela · Answer

start and end are datetimepickers. can we change the format of datetime pickers.is it something to do with this... thanks for helping me out from this .. I was really getting frustated from last 2 days it simple logic but I am missing something.

Iftikar Hussain · Answer

what is the value for start and end you are passing? Regards Iftikar

kiran vontela · Answer

datetime acceptedimg rejected img changeimg 16/06/2010 07:43 3011099 802236 5976268

Iftikar Hussain · Answer

what output you are getting now? Regards Iftikar

kiran vontela · Answer

datetime acceptedimg rejected img changeimg 16/06/2010 07:43 3011099 802236 5976268 but my output must be in date wise not to be in one column on 16/06/2010. I have 2 datetime pickers. that range of dates should be displayed right.

kiran vontela · Answer

ID User id location idplate action message datetime 18 1 1 KR54SWJ 2 Accepted Images 2007-08-07 14:17:41 17 1 1 EJ03YXL 2 Accepted Images 2007-08-07 14:17:30 16 1 1 K244MSB 2 Accepted Images 2007-08-07 14:17:24 8 1 1 EN02WRL 2 Accepted Images 2007-08-07 14:06:25 9 1 1 E1ONR 2 Accepted Images 2007-08-07 14:08:20 10 1 1 CK51KNR 2 Accepted Images 2007-08-07 14:09:09

kiran vontela · Answer

18 1 1 KR54SWJ 2 Accepted Images 2007-08-07 14:17:41 17 1 1 EJ03YXL 2 Accepted Images 2007-08-07 14:17:30 16 1 1 K244MSB 2 Accepted Images 2007-08-07 14:17:24 8 1 1 EN02WRL 2 Accepted Images 2007-08-07 14:06:25 9 1 1 E1ONR 2 Accepted Images 2007-08-07 14:08:20 10 1 1 CK51KNR 2 Accepted Images 2007-08-07 14:09:09

Iftikar Hussain · Answer

try like this MySqlDataAdapter sda = new MySqlDataAdapter(String.Format( SELECT datetime  + sum(CASE action when '2' then 1 else 0 end) as AcceptedIMG  + sum(CASE action when '3' then 1 else 0 end) as RejectedIMG  + sum(CASE action when '4' then 1 else 0 end) as ChangeIMG  + FROM customer_1.audit_trail  + where DATE(datetime)>= '{0}' and DATE(datetime)  start.Value.ToString(yyyy-MM-dd) end.Value.ToString(yyyy-MM-dd) ) conn) Regards Iftikar

Iftikar Hussain · Answer

can you provide some sample date from customer_1.audit_trail? Regards Iftikar

kiran vontela · Answer

NO luck Iftikar all columns are zeroes with only one row with no date.

Muralidharan Deenathayalan · Answer

Can you please post same sample datetime ? You may need to convert the datetime data into date.

kiran vontela · Answer

Yes murali datetime is field in my table

Iftikar Hussain · Answer

Hi try like this MySqlDataAdapter sda = new MySqlDataAdapter(String.Format( SELECT datetime  + sum(CASE action when '2' then 1 else 0 end) as AcceptedIMG  + sum(CASE action when '3' then 1 else 0 end) as RejectedIMG  + sum(CASE action when '4' then 1 else 0 end) as ChangeIMG  + FROM customer_1.audit_trail  + where datetime BETWEEN '{0}' and '{1}' start.Value.ToString(MM/dd/yyyy) end.Value.ToString(MM/dd/yyyy) ) conn) Regards Iftikar

Muralidharan Deenathayalan · Answer

Do you store time in database?