SqlDateTime overflow exception (yyyy-mm-dd to sqldatetime)

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); 

4 Answers

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

You can do like this

string expirationDate = GetDate(); string date = Convert.DateTime(expirationDate).ToString("yyyy-MM-dd"); 

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); 

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.