sql datetime parameter problem

Question

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)

how do you pass the paramtere; using querystring of command object ? — Binil
– Binil, Commented Dec 31, 2010 at 5: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
– Waleed Al-Balooshi, Commented Dec 31, 2010 at 5: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
– 3Dave, Commented Dec 31, 2010 at 5: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
– marc_s, Commented Dec 31, 2010 at 7:29

Anthony Faull · Accepted Answer · 2010-12-31 23:56:34Z

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.

edited Dec 31, 2010 at 23:56

answered Dec 31, 2010 at 16:44

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Philip Fourie · Accepted Answer · 2010-12-31 05:56:04Z

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

answered Dec 31, 2010 at 5:56

Philip Fourie

118k11 gold badges69 silver badges84 bronze badges

1 Comment

Jims Over a year ago

Philip Fourie->Hi still getting that exception

Jezbers · Accepted Answer · 2013-04-10 14:15:27Z

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?

edited Apr 10, 2013 at 14:15

answered Apr 9, 2013 at 20:00

Jezbers

81410 silver badges22 bronze badges

3 Comments

ErikE Over a year ago

This answer is basically identical to one already posted. Why post this over 2 years later?

Jezbers Over a year ago

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.

ErikE Over a year ago

Mentioning something in your answer about why you added one extra "M" would be kinda important...

Collectives™ on Stack Overflow

sql datetime parameter problem

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related