datetime field with a getdate() default

[cfw2]

I have a stored procedure that runs nightly, called by a batch command on my database server. The procedure logs everything it does into a log table:

create table log_tab 
(
 logdate smalldatetime
 ,logevent varchar(200)
 )

both fields are populated through the procedure as such:

insert into log_tab (logdate, logevent) 
select 
 getdate()
 ,'Procedure Ran Successfully'

yesterday I added a "dateadded" and "timeadded" field (I know this seems redundant with the logdate field).

Both fields have defaults that auto-populate the date and time:

alter table log_tab
add dateadded smalldatetime constraint log_df01 default convert(varchar(11),getdate())
 ,timeadded datetime constraint log_df02 default convert(varchar(11),getdate())

this morning when I checked the log, I noticed that there was roughly a 30 minute time difference between the logdate and timeadded fields. The really weird part, is that the timeadded field had a time stamp of 30 minutes prior to when the transaction actually took place. ex. the logdate field has a value of 2007-07-25 21:44 for one record, and timeadded field has a value of 2007-07-25 21:07 for the same record. I am certain that the logdate field is the correct value based on the time the procedure is executed through the batch program.

Does anyone have any idea why there would be such a large discrepancy between the default vs. the insert?

Thank you,
Charles

 

[ESquared]

How long does the insert query take to run? Is it possible it is a 30 minute query? That could explain, since the insert statement GetDate() would be evaluated at execution time and the table default would be evaluated at actual insertion time.... just speculating.

Another could be a difference between computers/servers times. Check that all the computers involved have the right times. This may sound strange but weird things can happen, especially if distributed transactions are involved (although you haven't indicated that this is the case).

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]