CODEWITHSUNDEEP
c#sqldatetime
Jims
Jims

Reputation: 1

sql datetime parameter problem

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)

Upvotes: 0

Views: 1453

Answers (3)

Jezbers
Jezbers

Reputation: 814

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?

Upvotes: 0

Anthony Faull
Anthony Faull

Reputation: 17997

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.

Upvotes: 2

Philip Fourie
Philip Fourie

Reputation: 117017

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

Upvotes: 0

Related Questions