Filter Crystal Report between two Dates from VARCHAR column in ASP.Net

Hi Kirron,

Check the below example.

SQL

CREATE TABLE Employees
(EmployeeID INT IDENTITY PRIMARY KEY,FirstName VARCHAR(20),LastName VARCHAR(50),BirthDate VARCHAR(50))

INSERT INTO Employees VALUES('Lakshmi','Devi','08-07-2018')
INSERT INTO Employees VALUES('Chandru','Kmatagi','08-07-2018')
INSERT INTO Employees VALUES('Nurani','Shedaji','08-07-2018')
INSERT INTO Employees VALUES('Nurani','Shedaji','08-07-2018')
INSERT INTO Employees VALUES('Design','Charge','08-07-2018')
INSERT INTO Employees VALUES('Arun','','08-07-2018')
INSERT INTO Employees VALUES('Honnutagi','Banner','08-08-2018')
INSERT INTO Employees VALUES('B.L.D.E','College','08-08-2018')
INSERT INTO Employees VALUES('B.L.D.E','College','25-08-2018')

HTML

<asp:TextBox ID="txtFromDate" runat="server" Text="01-08-2018"></asp:TextBox>
<asp:TextBox ID="txtToDate" runat="server" Text="08-08-2018"></asp:TextBox>
<asp:Button ID="btnSearchTerm" runat="server" Text="Filter" OnClick="SearchClick" />
<br />
<CR:CrystalReportViewer ID="crPersonInformation" runat="server" AutoDataBind="true"
    EnableDatabaseLogonPrompt="False" EnableParameterPrompt="False" />

 Namespaces

C#

using System;
using System.Configuration;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;

VB.Net

Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Code

C#

protected void SearchClick(object sender, EventArgs e)
{
    string fromDate = txtFromDate.Text.Trim();
    string toDate = txtToDate.Text.Trim();
    ReportDocument crystalReport = new ReportDocument();
    crystalReport.Load(Server.MapPath("~/EmployeesDetail.rpt"));
    Employees dsPersonInformations = GetEmployees(fromDate, toDate);
    crystalReport.SetDataSource(dsPersonInformations);
    crPersonInformation.ReportSource = crystalReport;
}

private Employees GetEmployees(string fromDate, string toDate)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand("SELECT * FROM Employees WHERE CONVERT(DATETIME,BirthDate,103) BETWEEN CONVERT(DATETIME,@FromDate,103) AND CONVERT(DATETIME,@ToDate,103)");
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@FromDate", fromDate);
            cmd.Parameters.AddWithValue("@ToDate", toDate);
            sda.SelectCommand = cmd;
            using (Employees dsEmployees = new Employees())
            {
                sda.Fill(dsEmployees, "Employee");
                return dsEmployees;
            }
        }
    }
}

VB.Net

Protected Sub SearchClick(ByVal sender As Object, ByVal e As EventArgs)
    Dim fromDate As String = txtFromDate.Text.Trim()
    Dim toDate As String = txtFromDate.Text.Trim()
    Dim crystalReport As ReportDocument = New ReportDocument()
    crystalReport.Load(Server.MapPath("~/EmployeesDetail.rpt"))
    Dim dsPersonInformations As Employees = GetEmployees(fromDate, toDate)
    crystalReport.SetDataSource(dsPersonInformations)
    crPersonInformation.ReportSource = crystalReport
End Sub

Private Function GetEmployees(ByVal fromDate As String, ByVal toDate As String) As Employees
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Employees WHERE CONVERT(DATETIME,BirthDate,103) BETWEEN CONVERT(DATETIME,@FromDate,103) AND CONVERT(DATETIME,@ToDate,103)")
    Using con As SqlConnection = New SqlConnection(conString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@FromDate", fromDate)
            cmd.Parameters.AddWithValue("@ToDate", toDate)
            sda.SelectCommand = cmd
            Using dsEmployees As Employees = New Employees()
                sda.Fill(dsEmployees, "Employee")
                Return dsEmployees
            End Using
        End Using
    End Using
End Function

Screenshot