none
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Question

  • Hi,

       I am working on C#.net project while insert date in Sql server 2000 and when i fire select query between date it gives me this Error:

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Would you please help me why it comes and how i can remove it as soon as posible.

    Thanks.

    Pankaj Sharma

    Wednesday, March 30, 2011 12:01 AM

All replies

  • is this a sql server error or a c# error?

    Wednesday, March 30, 2011 9:28 AM
  • Please post your code or we will not be able to troubleshoot the issue.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, March 30, 2011 12:23 PM
  • Hi,

    Based on the information proved by you, I can foresee two possible places where your code could be breaking:

    1. The date format that you pick-up from UI may be DD/MM/YYYY and the sql DB may be expecting it MM/DD/YYYY format (this is common in case of indian applications)
    2. Else it could be something as specified in this link

    what I recommend you will be look into this article for a possible workaround.

    Do reply we want to hear from you. :)


    Manish Patil http://patilmanishrao.wordpress.com Posting is provided AS IS with no warranties, and confers no rights.

    Manish Patil's Blog

    ↑ Grab this Headline Animator

    Thursday, March 31, 2011 3:06 PM
  • Hi,

         It is an SQL server Error as i think.

    I have an another problem i am creating crystal report and I'm trying to put RecordSelectionFormula on Date but it send an error :

    "Error in selection Formula :the remaining text does not appear to be part of the formula "

    My Code is:

                FrmReportAll f = new FrmReportAll();

                objRpt = new RptEnqStudent();

                f.MdiParent = this.MdiParent;

                f.WindowState = FormWindowState.Maximized;

                f.crystalReportViewer1.Visible = true;

                f.crystalReportViewer1.Dock = DockStyle.Fill;

                objRpt.RecordSelectionFormula = " {Enquiry.EnquiryDate} >="+DtpFormDate.Value;

                f.crystalReportViewer1.ReportSource = objRpt;

                f.crystalReportViewer1.Refresh();

                f.Show();

     

    Actually I want data on crystal report between 2 date i started but i got this error would you please help how i can apply RecordSelectionFormula on date field.

    Thanks,

    Pankaj Sharma

    Tuesday, April 05, 2011 12:51 AM
  • if I remember correctly, you should format your date in a culture independent format;

    "DateOfBooking = #" + (somedatevar).ToString(Globalization.CultureInfo.InvariantCulture) + "#"


    Tuesday, April 05, 2011 5:02 PM
  • Hi,

      Thanks for your help but after applied date in culture independent format still i got the same error.

    Like:

    "objRpt.RecordSelectionFormula = "{Enquiry.EnquiryDate}>="+ DtpFormDate.Value.ToString(System.Globalization.CultureInfo.InvariantCulture) ;"

     

    Then what should i do.

    Thanks,

    Pankaj Sharma

    Saturday, April 09, 2011 12:56 AM
  • objRpt.RecordSelectionFormula = "EnquiryDate >= #"+ DtpFormDate.Value.ToString(System.Globalization.CultureInfo.InvariantCulture) + "#" ;

    this is how it needs to be; dates need to be between cross signs.
    Saturday, April 09, 2011 1:10 AM
  • Hi 

      Now i was able to fix the error as suggest me use cross signs.

    I have 1 query as i think cross signs are used in MS-Access queries is right or am i wrong.

    But after this i was facing  one problem as i pass date to RecordSelectionFormula some time it gives records and some time not.

    It was not giving me proper output.  May be any of reason :would you please correct me if i am wrong..

    1.In SQL date store date with time and i was compare it with only date.

     

    Should i remove time from date field. Does it creates problem.

    I tried all pattern  i removed time from date field and then pass to RecordSelectionFormula but some time it gives me proper output.

    Would you please let me know in which formate i should store date in query if you have any special views.

    Thanks,

    Pankaj Sharma

    Tuesday, April 12, 2011 4:40 AM
  • it's a common problerm: most of the time, dates need to be compared on a date level, not on time level:

    objRpt.RecordSelectionFormula = "EnquiryDate >= #"+ DtpFormDate.Date.Value.ToString(System.Globalization.CultureInfo.InvariantCulture) + "#" ;

     

    Tuesday, April 12, 2011 5:24 AM