Error In Updating A Table Using Datetime As Parameter In Stored Procedure

The conversion of a char data type to a DateTime data type resulted in an out-of-range DateTime value. The statement has been terminated. ALTER PROCEDURE [dbo].[attendance_updateby

Solution 1:

From your code, it seems you have a DateTime variable in your C# code - so just use that as is - there's absolutely no need to convert to a string!!

If you have:

ALTERPROCEDURE [dbo].[attendance_updatebyemployee_id]
  @Employee_id int,
  @AtDate datetime,
.....

then you can call this stored like this:

cmd.Parameters.AddWithValue("@AtDate", Dtime);

assuming Dtime is of type DateTime in C#.

Don't convert and cast stuff unnecessarily! This only introduces potential breakages and conversion errors! Just use the DateTime as is - it will be sent to SQL Server and used and recognized as a DATETIME in T-SQL.

And if you really do need to convert a date value to a string for passing it to SQL Server, I would always recommend using the ISO-8601 standard format: YYYYMMDD or yyyy-MM-ddThh:mm:ss which is recognized and works regardless of any regional, language or locale settings.

So if you absolutely must, then use:

Dtime.ToString("yyyyMMdd")

or

Dtime.ToString("yyyyMMddTHH:mm:ss")

and nothing else (all other formats are language- and setting-dependent and might work in one case, and break in another; ISO-8601 always works)

Solution 2:

Update

You formatting string should be as

ToString("MM\\/dd\\/yyyy hh:mm:ss tt")

this will give output as in your database

02/19/2011 12:00:00 AM

---

You can use DateTime.TryParse() to convert a string to a DateTime object.

However you need to ensure that string is infact a datetime representation.

Example (From MSDN)

string[] dateStrings = {"05/01/2009 14:57:32.8", "2009-05-01 14:57:32.8", 
                        "2009-05-01T14:57:32.8375298-04:00", 
                        "5/01/2008 14:57:32.80 -07:00", 
                        "1 May 2008 2:57:32.8 PM", "16-05-2009 1:00:32 PM", 
                        "Fri, 15 May 2009 20:10:57 GMT" };
DateTime dateValue;

Console.WriteLine("Attempting to parse strings using {0} culture.", 
                CultureInfo.CurrentCulture.Name);
foreach (string dateString in dateStrings)
{
if (DateTime.TryParse(dateString, out dateValue)) 
    Console.WriteLine("  Converted '{0}' to {1} ({2}).", dateString, 
                        dateValue, dateValue.Kind);
else
    Console.WriteLine("  Unable to parse '{0}'.", dateString);
}
// The example displays the following output://    Attempting to parse strings using en-US culture.//       Converted '05/01/2009 14:57:32.8' to 5/1/2009 2:57:32 PM (Unspecified).//       Converted '2009-05-01 14:57:32.8' to 5/1/2009 2:57:32 PM (Unspecified).//       Converted '2009-05-01T14:57:32.8375298-04:00' to 5/1/2009 11:57:32 AM (Local).//       Converted '5/01/2008 14:57:32.80 -07:00' to 5/1/2008 2:57:32 PM (Local).//       Converted '1 May 2008 2:57:32.8 PM' to 5/1/2008 2:57:32 PM (Unspecified).//       Unable to parse '16-05-2009 1:00:32 PM'.//       Converted 'Fri, 15 May 2009 20:10:57 GMT' to 5/15/2009 1:10:57 PM (Local).

---

If you know what is the format of the date you are trying to Parse then you can use DateTime.TryParseExact() and specify your formatting in the second parameter:

DateTime.TryParseExact(dateString, "M/dd/yyyy hh:mm", enUS, 
                         DateTimeStyles.None, out dateValue)

Solution 3:

If you have the tool use the SQL Profiler to actually see the exact SQL string that is passed to the database. This will help you to find the error.

Solution 4:

Its just a matter of matching the data type in 3 places:

• The type your C# passes to the Stored Procedure
• The type your Store Procedure is expecting it
• The type you field is in the database

Currently,

• You C# is passing a string value Dtime.ToString("dd/MMM/yyyy")
• Your Stored Procedure is expecting a datetime (@AtDate datetime)
• Your Atdate field in database seems to be datetime (Atdate=@AtDate)

So, to make them all match, you only have to adjust your C# code, from string to Dtime, like this:

cmd.Parameters.AddWithValue("@AtDate",Dtime);

And i hope the Atdate field in your database is actually a DATETIME, not a VARCHAR. If it is a VARCHAR, then you should keep your C# code as it is, and change the @AtDate parameter in the Stored Procedure match it:

@AtDate VARCHAR(10)

But the best, if possible, is to make them all a true DATETIME. Avoid using strings (varchar) for dates if you can