GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:

> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession @.nField1 int = 0,
@.nField2 int = 0,
@.nField3 int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking( fldField1,
fldField2,
fldField3,
fldField4)
VALUES ( @.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction @.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql