-2

I'm extracting a date-formatted string (yyyy-MM-dd) and then converting it to a DateTime and inserting it in the database using a storedprocedure. But I keep getting

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

The column in my table is of type date.

I convert my string (e.g 2016-01-15) by invoking DateTime.ParseExact(expirationDate, "yyyy-MM-dd", null) and then I take that value and insert it as a parameter into my StoredProcedure and execute the proc.

But when using the debugger I can see that the return value is actually a yyyy-MM-dd HH:mm:ss-formatted DateTime.

this is an example of how the code looks like

string expirationDate = GetDate();
DateTime date = DateTime.ParseExact(expirationDate, "yyyy-MM-dd", null);

ExecuteMyStoredProc(date);
user1021726
  • 638
  • 10
  • 23
  • @shashwat: I saw that thread but it didn't seem to be an exact replica of my problem (though I did try some of their solutions). So whoever edited my question may please remove their edit... – user1021726 Jan 28 '14 at 09:09
  • http://stackoverflow.com/questions/19018600/datetime-issue-in-sql-server See this link. It might help. – bluejaded Jan 28 '14 at 09:15
  • 1
    `DateTime`s don't *have* a format. They're a count of 100-nanosecond intervals since midnight on 01/01/0001. When you say "is actually a `yyyy-MM-dd HH:mm:ss`-formatted DateTime." what you're actually saying is that when *something* (debugger, output window, a control, etc) converts the `DateTime` into a string, to actually display it to you, it's using some particular format. That's an artifact of whatever's performing the conversion, not something inherent to the `DateTime` value. – Damien_The_Unbeliever Jan 28 '14 at 09:18

4 Answers4

1
DateTime convertedInitDate = DateTime.ParseExact(initDate, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture);
      cCCBEntities.initDate = convertedInitDate;

The code above is the code I used when I encountered this same problem that I kept on looking for like 5 hrs already. Thankfully mine got solved alright with the above code.

Notes:

  1. I was using MVC in Visual Studio to develop a web application using sql server
  2. cCCCBEntities is the name of my Entity I was using
  3. This answer was the answer I got above from user326608, I just cant vote on it yet so i decided to put it on a comment here to emphasize that its what it solved my problem
  4. Also, even though I was using SPs when saving to DB too, I didnt have to follow his recommendation about altering the SP, if you still encounter a problem, then follow user326608 instructions about modifying your SP code
iamjoshua
  • 1,157
  • 3
  • 16
  • 33
0

Just try like this

    string expirationDate = DateTime.Now.ToString();
    string date = Convert.ToDateTime(expirationDate).ToString("yyyy-MM-dd");
    DateTime dt = DateTime.ParseExact(date, "yyyy-MM-dd", null);
    ExecuteMyStoredProc(dt);
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
0

You can do like this

string expirationDate = GetDate();
string date = Convert.DateTime(expirationDate).ToString("yyyy-MM-dd");
Chirag
  • 324
  • 2
  • 4
  • 27
0

First to get a meaningful date value in code:

DateTime myDate = DateTime.ParseExact(expirationDate, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture)

But, you'll still possibly get a time value in the debugger for myDate, as its a DateTime object.

Your SP is the next problem. Change it to something like:

INSERT INTO MyTable (MyDate) Values (Convert(DateTime,@expiration_date,111)) --yyyy/mm/dd

The convert codes for SQL Server are here.

user326608
  • 2,210
  • 1
  • 26
  • 33