Incorrect DateTime.Now when saved to SQL

[John J. Hughes II]

I have several functions that save the time when they did certain tasks to
an SQL table. This was working fine as far as I could tell but I now have
systems that are updating thousands of records and a very small percentage
are showing as future dates by seconds to hours. As a guess I would say
about 90% are correct

Does any body see what I am doing wrong?

Below is the basic code:

In the SQL I have the following stored procedure:

Create Procedure InsertData
(
@F1 nvarchar(100),
@F2 datetime
)
AS
SET NOCOUNT OFF;
UPDATE TBL1 SET [F2]=@F2 WHERE [F1]=@F1

C# Code

SqlCommand cmd = ("[InsertData]", this.sqlConnection))
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@F1", SqlDbType.NVarChar, 100);
cmd.Parameters["@F1"].value = "something".
cmd.Parameters.Add("@F2", SqlDbType.DateTime);
cmd.Parameters["@F2"].value = DateTime.Now.
cmd.ExecuteNonQuery();

Regards,
John

 

[Dan Bass]

how long does it take to up date thousands of records?

would you expect the date to be the same for each one? Because the code
below will take the date at the time just before each command was able to
execute.

If you wanted to update the records with some date of some event, then you
should save this into a DateTime object, then reference this, rather than
referencing DateTime.Now every time.

eg.
DateTime eventTime = DateTime.Now;

// loads of other code perhaps

SqlCommand cmd = ("[InsertData]", this.sqlConnection))
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@F1", SqlDbType.NVarChar, 100);
cmd.Parameters["@F1"].value = "something".
cmd.Parameters.Add("@F2", SqlDbType.DateTime);
cmd.Parameters["@F2"].value = eventTime;
cmd.ExecuteNonQuery();

 

[Guest]

How many machines run the update sp ?
When using System.DateTime.Now you are depending on the clocks of the
individual PCs running the .NET code.

For conformity you may want to use the SQL Server clock by using GETDATE()

Best regards,

Nico De Greef

 

[John J. Hughes II]

Thanks for your response, see in line
John

how long does it take to up date thousands of records?

I am only changing the date to one record at a time, the thousands are being
updated during the course of the day so 24 hours. Two records could be
updated a few millisecond apart and a few minutes. The might also be update
from different threads.

would you expect the date to be the same for each one? Because the code
below will take the date at the time just before each command was able to
execute.

No I expect the date and time to be different. I am only trying to tell the
system when the server changed the record.

If you wanted to update the records with some date of some event, then you
should save this into a DateTime object, then reference this, rather than
referencing DateTime.Now every time.

In places where I save the same date to multiple records I do that and seems
to have the same problem. In this case the time span from when I save the
date to an object to when I used it would be too long.

eg.
DateTime eventTime = DateTime.Now;

// loads of other code perhaps

SqlCommand cmd = ("[InsertData]", this.sqlConnection))
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@F1", SqlDbType.NVarChar, 100);
cmd.Parameters["@F1"].value = "something".
cmd.Parameters.Add("@F2", SqlDbType.DateTime);
cmd.Parameters["@F2"].value = eventTime;
cmd.ExecuteNonQuery();

I have several functions that save the time when they did certain tasks to
an SQL table. This was working fine as far as I could tell but I now have
systems that are updating thousands of records and a very small percentage
are showing as future dates by seconds to hours. As a guess I would say
about 90% are correct

Does any body see what I am doing wrong?

Below is the basic code:

In the SQL I have the following stored procedure:

Create Procedure InsertData
(
@F1 nvarchar(100),
@F2 datetime
)
AS
SET NOCOUNT OFF;
UPDATE TBL1 SET [F2]=@F2 WHERE [F1]=@F1

C# Code

SqlCommand cmd = ("[InsertData]", this.sqlConnection))
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@F1", SqlDbType.NVarChar, 100);
cmd.Parameters["@F1"].value = "something".
cmd.Parameters.Add("@F2", SqlDbType.DateTime);
cmd.Parameters["@F2"].value = DateTime.Now.
cmd.ExecuteNonQuery();

Regards,
John

 

[John J. Hughes II]

Thanks for the response,

The reason I am doing it the way I am doing it is I want the date of the
local machine. Also in some places I am writing the same date to a group of
records and yes as Dan pointed out I save it to an object first.

In the case of the problem being reported to me by my customer the SQL
Server and my application are running on the same computer so I would expect
the dates to be the same or at least very close. The date differencesI am
seeing is up to an hour different.

Regards,
John

How many machines run the update sp ?
When using System.DateTime.Now you are depending on the clocks of the
individual PCs running the .NET code.

For conformity you may want to use the SQL Server clock by using GETDATE()

Best regards,

Nico De Greef

I have several functions that save the time when they did certain tasks
to
an SQL table. This was working fine as far as I could tell but I now
have
systems that are updating thousands of records and a very small
percentage
are showing as future dates by seconds to hours. As a guess I would say
about 90% are correct

Does any body see what I am doing wrong?

Below is the basic code:

In the SQL I have the following stored procedure:

Create Procedure InsertData
(
@F1 nvarchar(100),
@F2 datetime
)
AS
SET NOCOUNT OFF;
UPDATE TBL1 SET [F2]=@F2 WHERE [F1]=@F1

C# Code

SqlCommand cmd = ("[InsertData]", this.sqlConnection))
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@F1", SqlDbType.NVarChar, 100);
cmd.Parameters["@F1"].value = "something".
cmd.Parameters.Add("@F2", SqlDbType.DateTime);
cmd.Parameters["@F2"].value = DateTime.Now.
cmd.ExecuteNonQuery();

Regards,
John