0

Hi I am getting this error:

Writing record number:

60479System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I have tried this :

try
{
     DateTime dt = Convert.ToDateTime(fireBirdReader["DUEDATE"]);
     cmd.Parameters.AddWithValue("@DUEDATE", fireBirdReader["DUEDATE"]);
}
catch
{
     cmd.Parameters.AddWithValue("@DUEDATE", DateTime.Now);
}

Also I have tries this:

DateTime temp;
if(DateTime.TryParse(startDateTextBox.Text, out temp))
{
cmd.Parameters.AddWithValue("@DUEDATE", temp);
}
else
{
cmd.Parameters.AddWithValue("@DUEDATE", DateTime.Now);
}

But none of them working. I am using c# and console application.

Why you are marking as duplicate that answer is not working in my case. Can you please test it yourself. Do not try to over smart before try. I can also search google and stackoverflow.

Tony smith
  • 49
  • 3
  • 13
  • What is the value of `dt` exactly? Have you ever consider to use `datetime2` as a column type instead? By the way, you parse your `fireBirdReader["DUEDATE"]` to `DateTime` but you _still_ use `fireBirdReader["DUEDATE"]` in your `AddWithValue`, **not** `dt`. – Soner Gönül Mar 15 '16 at 10:01
  • I am getting Invalid values like: 04.10.1011, 00:00:00.000 – Tony smith Mar 15 '16 at 10:04
  • I suggest stop using `AddWithValue`, instead use `cmd.Parameters.Add("@DUEDATE", SqlDbType.DateTime).Value = DateTime.Now;` – Hari Prasad Mar 15 '16 at 10:07
  • Why you have marked as duplicate the thread you have referenced is not working for my condition – Tony smith Mar 15 '16 at 10:16
  • I agree it's not a duplicate (I voted to reopen). My guess is that `fireBirdReader["DUEDATE"]` is not in a format that Convert.toDateTime() understands and is therefore outputting gibberish. Can you tell us what type & value it is ? – Eric Darchis Mar 15 '16 at 10:52
  • Already told that : 04.10.1011, 00:00:00.000 – Tony smith Mar 15 '16 at 11:00
  • @EricDarchis Why do you think it is _not_ a duplicate? OP's value (which is `04.10.1011`) is clearly out of range on `datetime` type which has `1/1/1753` and `12/31/9999`. And more important, OP should _not_ supply this parameter as a `string` but `DateTime`. – Soner Gönül Mar 15 '16 at 11:04
  • @SonerGönül The value is out of range, there is no question about it. The fact is that @TonySmith tried to convert his value with `Convert.ToDateTime(fireBirdReader["DUEDATE"]);` and it didn't seem to help. However, the result of the Convert (dt) is not used in the next line for AddWithValue. It is also not clear what the value of `fireBirdReader["DUEDATE"]` actually is *before* converting to dateTime. – Eric Darchis Mar 15 '16 at 13:24
  • @EricDarchis _and it didn't seem to help_ What do you mean by that? If OP couldn't parse this `fireBirdReader["DUEDATE"]` to `DateTime`, he would get `FormatException` on _that_ line. But `SqlTypeException` throwed from SQL Server, not C#. That means, OP successfully parsed this value to `DateTime`. As you said we don't know _exact_ value of this string. It could be `04.10.1011 00:00:00.000` or `04.10.1011` or `04/10/1011` etc.. Only we know that OP tried to sends a `string` to a `datetime` column, SQL Server successfully parse this string but it is out of range _no matter_ what format it has. – Soner Gönül Mar 15 '16 at 14:42

1 Answers1

0

I think you should parse the DateTime value to a string, after the TryParse and before passing it to the command query object as an argument.

dateTime.ToString("yyyy-MM-ddTHH:mm:ss.fff")
lucacelenza
  • 1,259
  • 1
  • 15
  • 28