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.|||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.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>