PRB: Inserting a Date/Time Value into SQL Server with ADO Loses Milliseconds (246438)

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a Microsoft Visual C++ console application.
2. Paste the following code into your implementation file:
   
   Note You must change the User ID=<username> value and the password =<strong 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.

   #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=<username>;Password=<strong 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;
   }
   						

3. Change the connect string to reflect your SQL Server Pubs database
4. Compile, set a breakpoint (as indicated in the code comments), and run the code.
5. 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:
   
   
   1. Using _RecordsetPtr->AddNew() the milliseconds are truncated when the value is stored in SQL Server.
   2. Using _ConnectionPtr->Execute() the milliseconds are preserved and correctly stored in SQL Server.

Workaround