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

• ActiveX Data Objects (ADO), version 2.1

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.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a Microsoft Visual C++ console application.



2. Paste the following code into your implementation file:

   
   #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=sa;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

Additional query words:

Keywords : kbADO kbADO210 kbDatabase kbMDAC kbGrpVCDB kbDSupport
Version : WINDOWS:2.1
Platform : WINDOWS
Issue type : kbprb