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

Answer 1

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

Answer 2

Please post your code or we will not be able to troubleshoot the issue.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 3

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.

↑ Grab this Headline Animator

Answer 4

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

Answer 5

if I remember correctly, you should format your date in a culture independent format;

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

Answer 6

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

Answer 7

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

this is how it needs to be; dates need to be between cross signs.

Answer 8

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

Answer 9

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) + "#" ;