Error updating table using date and time as a parameter in stored procedure

Question

Converting a char data type to a DateTime data type resulted in a DateTime value out of range. Application completed.

ALTER PROCEDURE [dbo].[attendance_updatebyemployee_id]
@Employee_id int,
@AtDate datetime,
@FNLogged bit,
@ANLogged bit,
@LogTime varchar(10),
@LogOuttime varchar(10) 

AS
BEGIN   


    SET NOCOUNT ON;    
    update Mst_Attendance set FNLogged=@FNLogged,
    ANLogged=@ANLogged,LogTime=@LogTime,LogOuttime=@LogOuttime 
    where EmployeeId=@Employee_id and Atdate= @AtDate

END

in c # code i give it like

 cmd.Parameters.AddWithValue("@AtDate",Dtime.ToString("dd/MMM/yyyy"));

when using SQl profiles. Past data

exec [dbo].[attendance_updatebyemployee_id] @Employee_id=2,@AtDate='Feb 19 2011 12:00:00:000AM',@FNLogged=1,@ANLogged=0,@LogTime='11:45 AM',@LogOuttime=' ' 

inside the stored procedure @AtDate matters 2011-02-19 00:00:00.000. it breaks from the update command.

Inside the table, the date is saved as 2/19/2011 12:00:00 AM

How can I solve this problem with a date.

Answer 1

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

Answer 2

, DateTime #, - !!

:

ALTER PROCEDURE [dbo].[attendance_updatebyemployee_id]
  @Employee_id int,
  @AtDate datetime,
.....

, :

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

, Dtime DateTime #.

! ! DateTime - SQL Server DateTime T-SQL.

SQL Server, ISO-8601: YYYYMMDD yyyy-MM-ddThh:mm:ss , .

, , :

Dtime.ToString("yyyyMMdd")

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

( , ISO-8601 )

Answer 3

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

,

02/19/2011 12:00:00 AM

---

DateTime.TryParse() DateTime.

, .

( 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).

---

, , , DateTime.TryParseExact() :

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

Answer 4

:

• #
• , Store
• , ,

• , #, Dtime.ToString("dd/MMM/yyyy")
• datetime (@AtDate datetime)
• Atdate datetime (Atdate=@AtDate)

, , #, Dtime, :

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

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

@AtDate VARCHAR(10)

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