Microsoft KB Archive/246438

= PRB: Inserting a Date/Time Value into SQL Server with ADO Loses Milliseconds =

Article ID: 246438

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q246438



SYMPTOMS
When using an ADO Recordset to insert a date/time value into a SQL Server database, the millisecond portion of the value is truncated and the value is rounded off to the nearest second.



RESOLUTION
For a workaround see the "More Information" section.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
 Create a Microsoft Visual C++ console application.  Paste the following code into your implementation file:

Note You must change the User ID= value and the password = value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
 * 1) import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename( "EOF", "adoEOF" )

int main(int argc, char* argv[]) {

_ConnectionPtr pCon; _RecordsetPtr pRs; _variant_t varDate; double d;   _bstr_t btCon; _bstr_t btSQL;

CoInitialize(NULL);

try {

pCon.CreateInstance(__uuidof(Connection)); pRs.CreateInstance(__uuidof(Recordset));

btCon = L"Provider=SQLOLEDB.1;Persist Security Info=False;User ID= ;Password= ;Initial Catalog=Pubs;Data Source=(local)"; btSQL = L"SELECT * FROM employee WHERE fname='Test'"; pCon->Open(btCon,L"",L"",-1); pRs->Open(btSQL,pCon.GetInterfacePtr,               adOpenStatic,adLockOptimistic,-1); // THIS CODE TRUNCATES MILLISECONDS BY USING _RecordsetPtr->AddNew: pRs->AddNew; pRs->GetFields->GetItem(L"emp_id")->PutValue(L"ABC12345M"); pRs->GetFields->GetItem(L"fname")->PutValue(L"Test"); pRs->GetFields->GetItem(L"lname")->PutValue(L"Test"); d = 36438.409988773; //1999-10-05 09:50:23.030 varDate = d;       varDate.vt = VT_DATE; pRs->GetFields->GetItem(L"hire_date")->PutValue(varDate); pRs->Update; // SET BREAKPOINT ON NEXT LINE: pRs->Requery(-1); varDate = pRs->GetFields->GetItem(L"hire_date")->GetValue; d = varDate; // Value now rounded down to the nearest second. (36438.409988426)

// THIS CODE CORRECTLY INSERTS MILLISECONDS BY USING _ConnectionPtr->Execute: pCon->Execute(L"UPDATE employee SET hire_date = '1999-10-05 09:50:23.030' WHERE fname = 'Test'",&vtMissing,-1);

pRs->Requery(-1); varDate = pRs->GetFields->GetItem(L"hire_date")->GetValue; d = varDate; // Value now contains the correct value. (36438.409988773)       // Clean up: pRs->Delete(adAffectCurrent); }   catch (_com_error& e)    { HRESULT hr = e.Error; const TCHAR* szError = e.ErrorMessage; _bstr_t btDesc = e.Description; }   return 0; }                        Change the connect string to reflect your SQL Server Pubs database Compile, set a breakpoint (as indicated in the code comments), and run the code. In stepping through the code, you will see that setting the date to a value containing millisecond precision produces different outcomes depending upon the method used to insert the record:

 Using _RecordsetPtr->AddNew the milliseconds are truncated when the value is stored in SQL Server.</li> Using _ConnectionPtr->Execute the milliseconds are preserved and correctly stored in SQL Server.</li></ol> </li></ol>

Workaround
Use the Execute method of the Connection object to update/insert records with millisecond precision and use a string literal for the date value as outlined in the code earlier.

Keywords: kbdatabase kbprb KB246438

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.