0

HI all,

I am passing the date parameter as like this:

 DateTime date = DateTime.Now;
  date.ToString("YYYY-MM-DD 00:00:00:000");

But getting this exceptions:

System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753        12:00:00 AM and 12/31/9999 11:59:59 PM. at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value)
3Dave
  • 28,657
  • 18
  • 88
  • 151
Jims
  • 1
  • 1
  • http://stackoverflow.com/questions/468045/error-sqldatetime-overflow-must-be-between-1-1-1753-120000-am-and-12-31-9999 – Amjad Masad Dec 31 '10 at 05:45
  • how do you pass the paramtere; using querystring of command object ? – Binil Dec 31 '10 at 05:45
  • Can you post the code you use to persist the datetime to the database, because that is where the error would be. If you can actually also tell us what line of the code you pOst has the error, we will be able to fix this sooner – Waleed Al-Balooshi Dec 31 '10 at 05:46
  • The "code" you posted doesn't do anything. Please post the actual code that talks to the database (or a reasonable substitute). The `.ToString()` method won't generate a SQL exception. – 3Dave Dec 31 '10 at 05:46
  • Your .ToString() format is all wrong - you need to be more careful with your format string! Those characters are **case-sensitive**! You need to use `.ToString("yyyy-MM-dd")` - small `y` and `d` and capital `M` – marc_s Dec 31 '10 at 07:29

3 Answers3

2

There should be a decimal dot between the seconds and the milliseconds. The format string is case sensitive. Try:

date.ToString("yyyy-MM-dd")

or

date.ToString("yyyy-MM-dd HH:mm:ss.fff")

Also ask yourself whether you really need to convert arguments to strings. It smells odd and it may not be necessary. If you want to pass only the date and not the time, then pass the Date property of your DateTime object as your parameter value. Keep it strongly-typed to avoid SQL-injection, performance and type conversion issues.

Anthony Faull
  • 17,549
  • 5
  • 55
  • 73
0

If you are trying to ignore the time portion (hence your zeros) try 

date.ToString("yyyy-MMM-dd");

If you want the time portion too ...

date.ToString("yyyy-MMM-dd hh:mm:ss.fff tt");

Note both have 3 Ms for the month which makes them unambiguous strings that SQL should be able to parse and cannot misinterpret.

But, why not just pass the value as a date object rather than convert to a string?

Jezbers
  • 815
  • 10
  • 20
  • This answer is basically identical to one already posted. Why post this over 2 years later? – ErikE Apr 09 '13 at 20:02
  • didn't check the date! Actually is different. Everyone always insists in having months as 2 characters in strings. Use 3 characters, that way it is unambiguous, if you have to have strings at all of course. – Jezbers Apr 09 '13 at 20:04
  • Mentioning something in your answer about why you added one extra "M" would be kinda important... – ErikE Apr 09 '13 at 21:31
0

No parameter is being passed in here, the code sample you have posted is incomplete by looking at the resulting SqlTypeException

Also the date format should be:

date.ToString("yyyy-MM-dd HH:mm:ss:fff")
Philip Fourie
  • 111,587
  • 10
  • 63
  • 83