how to tackle with date format error

Question

0.00/5 (No votes)

See more:

Conversion failed when converting date and/or time from character string. This error is displayed.What to do

aspx.cs code :

C#

protected void btnGenerateReport_Click(object sender, EventArgs e)
{
    Label1.Visible = false;
    SqlCommand sc = new SqlCommand("GenerateReport", mycode.cn);
    sc.CommandType = CommandType.StoredProcedure;
    mycode.open();
    sc.ExecuteNonQuery();
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = sc;
    SqlCommandBuilder cm = new SqlCommandBuilder(da);
    DataSet ds = new DataSet();
    da.Fill(ds, "tblPersonalDetails");
    
    if (ds.Tables["tblPersonalDetails"].Rows.Count > 0)
    {
        GridView1.DataSource = ds.Tables["tblPersonalDetails"];
        GridView1.DataBind();
        GridView1.Visible = true;
    }
    else
    {
        Label1.Visible = true;
        Label1.Text = "Record Not found ";
        GridView1.Visible = false;
    }
}

aspx code :

ASP.NET

<asp:Label ID="lblStartDate" runat="server" Text="Start Date : ">
    <asp:TextBox ID="startDate" runat="server">    
    
 <asp:Label ID="lblEndDate" runat="server" Text="End Date : ">
    <asp:TextBox ID="endDate" runat="server">
    
<asp:Button ID="btnGenerateReport" runat="server" Text="Generate Report" 
        onclick="btnGenerateReport_Click" BackColor="#66CCFF" ForeColor="White" /><asp:Label ID="Label1" runat="server" Text="">
    <asp:ScriptManagerProxy ID="ScriptManagerProxy1" runat="server">
    
    <asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="startDate" Format="MM/dd/yyyy">
    
    <asp:CalendarExtender ID="CalendarExtender2" runat="server" TargetControlID="endDate" Format="MM/dd/yyyy">

stored procedure :

SQL

USE [HRRecruitment]
GO
/****** Object:  StoredProcedure [dbo].[GenerateReport]    Script Date: 03/18/2015 10:52:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GenerateReport]

as
begin
	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]

    select * from tblPersonalDetails Inner Join tblFamilyDetails
    on tblPersonalDetails.Email=tblFamilyDetails.Email Inner Join tblImageDetails
    on tblPersonalDetails.Email=tblImageDetails.Email Inner Join tblEducationalDetails
    on tblPersonalDetails.Email=tblEducationalDetails.Email Inner Join tblAcademicDetails
    on tblPersonalDetails.Email=tblAcademicDetails.Email Left Join tblWorkExpDetails
    on tblPersonalDetails.Email=tblWorkExpDetails.Email Left Join tblReferenceDetails
    on tblPersonalDetails.Email=tblReferenceDetails.Email Left Join tblDeclarationDetails
    on tblPersonalDetails.Email=tblPersonalDetails.Email where 
    tblPersonalDetails.JoinDate Between '" + startDate.Text + "' and '" + endDate.Text + "'
end;

Posted 17-Mar-15 21:04pm

Member 11304660

Updated 17-Mar-15 21:50pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

Reshma Babu 18-Mar-15 4:53am

Are you using
'" + startDate.Text + "' and '" + endDate.Text + "'
in your stored procedure?

Member 11304660 18-Mar-15 4:55am

i have also tried for 'startDate.text' and 'endDate.text'
But still the same error

Reshma Babu 18-Mar-15 4:59am

Actually, you are selecting 2 dates from the UI & you are passing that to the SP to generate report between those 2 dates right?

Member 11304660 18-Mar-15 5:00am

yes

Member 11304660 18-Mar-15 5:16am

thanks alot.. It worked

Reshma Babu 18-Mar-15 5:25am

welcome :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Reshma Babu · Accepted Answer · 2015-03-17T23:03:00

Your Stored Procedure should be something like this:

SQL

USE [HRRecruitment]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GenerateReport]
 @StartDate DATE,
 @EndDate DATE
as
begin

    select * from tblPersonalDetails Inner Join tblFamilyDetails
    on tblPersonalDetails.Email=tblFamilyDetails.Email Inner Join tblImageDetails
    on tblPersonalDetails.Email=tblImageDetails.Email Inner Join tblEducationalDetails
    on tblPersonalDetails.Email=tblEducationalDetails.Email Inner Join tblAcademicDetails
    on tblPersonalDetails.Email=tblAcademicDetails.Email Left Join tblWorkExpDetails
    on tblPersonalDetails.Email=tblWorkExpDetails.Email Left Join tblReferenceDetails
    on tblPersonalDetails.Email=tblReferenceDetails.Email Left Join tblDeclarationDetails
    on tblPersonalDetails.Email=tblPersonalDetails.Email where 
    tblPersonalDetails.JoinDate Between @StartDate and @EndDate 
end

And also add the two dates as the parameters to your SqlCommand: using
sc.Parameters.Add()