Actually, we were passing the datetime to the sql proc as a character string. The value was set using datetime() in foxpro. The we realized that we needed to use a standard datetime from the server so we added a line in the sql stored procedure to store the datetime to that variable using getdate() after a value was passed in. For some reason, when doing it that way, the time is rounded to whole minutes. It comes from VFP correct, but changing the value using getdate() rounded the minutes. I've never seen it happen before. So what we ended up doing as a quickfix was to just create a new variable in the stored proc, declaring the new variable there as datetime, and then using the new variable instead of the original parameter passed. Now the precision is maintained. Go figure! I don't get it. I just figured it was something to do with changing the value (and type) of a parameter in the stored procedure that I was not aware of.




>>Thanks Boris. It was in fact datetime, not smalldatetime. I did find the problem though. The datetime was passed as character string to the stored procedure and then in a fix (to use the datetime from the server) that variable was reset using GETDATE(). We switched to a different variable name, declared it as datetime and then stored the datetime to it using getdate() and it now is precise. Bad coding was the cause.
>>
>>
>>
>>>>Storing sql server data in a stored procedure setting datetime value by getdate() however, it doesn't seem to be storing seconds. Too many transactions within the same minute. Anyway to get a more precise datetime value in a stored procedure other than getdate()? We were passing the datetime from VFP but with many workstations the time could be wrong on each (that will change and the datetime will come from a server in the future) but for now, I need to store the current datetime (including seconds) to a variable in a stored procedure which then stores it to a record. When we look later at the data, all of the minutes are the same on many records so the order by clause on the datetime column is not precise enough.
>>>
>>>If you use smalldatetime, yes, that would be the result. Change field type to be datetime. This is not related to SQL Server you use (I think so, becuase I can't test it right now) but with the field type.
>
>You mean that if you have:*
>

>ldDateTime = DATETIME()
>SQLEXEC(sqlHandler, [EXEC MySP ?ldDateTime])
>

>
>and SP is something like:
>

>CREATE PROCEDURE MySP (@MyDateTime as datetime)
>AS
>BEGIN
>    SET @myDateTime = GETDATE()
>    INSERT INTO MyTable (DateField) VALUES (@myDateTime)
>END
>

>Where MyTable.DateField is datetime field you fill and you got the Date and Time but w/o secs?
>(I can't test this right now)
>But if this is true I consider it as a BUG.
>
>
>
>
>
>-------------------------------------------------------------------------------
>* VFP code is involved here, not related o SQL SERVER