current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -3 down vote favorite

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);
share|improve this question
    
@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 9:09
    
stackoverflow.com/questions/19018600/… See this link. It might help. –  bluejaded Jan 28 '14 at 9:15
1  
DateTimes 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 9:18
    
@Damien_The_Unbeliever ok, didn't know that –  user1021726 Jan 28 '14 at 9:26

3 Answers 3

up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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);
share|improve this answer
    
Are you suggesting to change the type on my column in the database? Because I can't do that. Or are you talking about some other kind of .NET DateTime object? –  user1021726 Jan 28 '14 at 9:20
    
I am asking you to change the your database column because it will accept minimum date –  Vignesh Kumar Jan 28 '14 at 9:21
    
I can't just change my database column, sorry –  user1021726 Jan 28 '14 at 9:22
    
@user1021726 I have updated solution. please check with that –  Vignesh Kumar Jan 28 '14 at 9:36
up vote 0 down vote

You can do like this

string expirationDate = GetDate();
string date = Convert.DateTime(expirationDate).ToString("yyyy-MM-dd");
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.