Click here to Skip to main content
15,914,322 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
kindly help me....i want to generate report between two dates selected from datetimepicker1 and datetimepicker2.....with this code m getting the problem that if i select 2 dates it show data till second last date....like if i select date between 17 to 20 it show data from date 17 to date 19.....help me plz
the datatype of column date in database is datetime....
C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace cargo
{
    public partial class Employee_Information : Form
    {
        SQLConnection db = new SQLConnection();
        public Employee_Information()
        {
            InitializeComponent();
        }

       

        private void Employee_Information_Load(object sender, EventArgs e)
        {
            db.DBConnect();
        }

        private void Employee_Information_FormClosed(object sender, FormClosedEventArgs e)
        {
            db.DBClose();
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            try
            {
              string fromdate = Convert.ToDateTime(dateTimePicker1.Text).ToString("dd-MMM-yyyy HH:mm:ss");
              string todate = Convert.ToDateTime(dateTimePicker2.Text).ToString("dd-MMM-yyyy HH:mm:ss");
                string str1 = "select * from employeeinfo where Date between '" + fromdate+ "' and  '" + todate + "' ";
                db.cmd.Connection = db.conn;
                db.cmd.CommandText = str1;
                SqlDataAdapter da = new SqlDataAdapter(str1, db.conn);
                da.Fill(db.ds, "employeeinfo ");
                CrystalReport1 cr = new CrystalReport1();
                cr.SetDataSource(db.ds.Tables[0]);
                crystalReportViewer1.ReportSource = cr;

            }
            catch (Exception ex)
            {
                MessageBox.Show("error in query" + ex.Message);
            }

        }

       
    }
}
Posted

1 solution

Hello

just see this

I have table that names TestTable and it has these many data
C#
1	Tejas	2011-09-19 14:54:11.613
2	Chirag	2011-09-12 14:54:11.633
3	Ankit	2011-09-23 14:54:11.633
4	Sagar	2011-09-15 14:54:11.633
5	Hiren	2011-09-28 14:54:11.633


now if i fire this query it will result this

C#
SELECT * FROM TestTable WHERE date between '2011-09-12' and '2011-09-19' ORDER BY date

2	Chirag	2011-09-12 14:54:11.633
4	Sagar	2011-09-15 14:54:11.633


but in actual there is also a record with name as tejas with 2011-09-19 date,
actually it will be there in the above query result but it is not there

why???

because the column date hase data type as datetime so it will store a date with time in hh:mm:ss:tt format for cheking you can fire this query

C#
print cast('2011-09-12' as datetime)

result is 

Sep 12 2011 12:00AM


so if we want the 2011-09-19 record will be seen in the query we want to give time also, but that time is greater then the record's time when it is inserted like for 2011-09-19 has the time of insertion is 14:54:11.613
so for getting that record we have to give a time like 14:55 or 15:00

see this query is..

C#
SELECT * FROM TestTable WHERE date between '2011-09-12' and '2011-09-19 14:55' ORDER BY date

result is 

2	Chirag	2011-09-12 14:54:11.633
4	Sagar	2011-09-15 14:54:11.633
1	Tejas	2011-09-19 14:54:11.613


now you understand why you cant get the desired result in your query..
hope this will help full to you... and don't forgot to accept the solution and also rate it..
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900