Incorrect DateTime.Now when saved to SQL

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

re: Incorrect DateTime.Now when saved to SQL

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



"John J. Hughes II" <no@invalid.com> wrote in message
news:%23xAhyW%23iFHA.3316@TK2MSFTNGP14.phx.gbl...[color=blue]
>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
>[/color]

re: Incorrect DateTime.Now when saved to SQL

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" wrote:
[color=blue]
> 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
>
>
>[/color]

re: Incorrect DateTime.Now when saved to SQL

Thanks for your response, see in line
John

"Dan Bass" <Not Listed> wrote in message
news:u58urSDjFHA.2904@tk2msftngp13.phx.gbl...[color=blue]
> how long does it take to up date thousands of records?[/color]

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.
[color=blue]
> 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.[/color]

No I expect the date and time to be different. I am only trying to tell the
system when the server changed the record.
[color=blue]
> 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.[/color]

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.
[color=blue]
> 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();
>
>
>
> "John J. Hughes II" <no@invalid.com> wrote in message
> news:%23xAhyW%23iFHA.3316@TK2MSFTNGP14.phx.gbl...[color=green]
>>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
>>[/color]
>
>[/color]

re: Incorrect DateTime.Now when saved to SQL

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

"Nico De Greef" <NicoDeGreef@discussions.microsoft.com> wrote in message
news:3F297471-AD38-4BBA-B383-6905412636C7@microsoft.com...[color=blue]
> 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" wrote:
>[color=green]
>> 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
>>
>>
>>[/color][/color]